Microsoft Excel - need some Macro help - Asked By anu anu on 30-Jan-14 01:41 AM

hi ,
 i have some data in excel file , i need some perticuler data from this data like 
 in base data in column G Metro\REmote location ,& in AL Col RT Details And col Ap DT details .

Now i need in next Work sheet2  only some data Copy 
1- if location is Metro And RT >2hrs 
2-if If Loction Remote-1 And Rt >4hs
3 iF location Remote -2 And Rt >NBD

And Sheet 3 I need 

If location Metro Ans Dt >4hrs
If Location Remote1 And Dt >6Hrs
If Remote-2 And Dt > NBD 

thanks 
Anu 

Hello Anu,
How about a sample of the data and the required output and what is going to trigger the data extraction?
Regards
Harry

Sample File.zip

Hi harry

pls find the same

regards
Anu

Hello Anu,
Try this.  I am not sure what you mean by >NBD so you will have to work the logic out on that one or explain it a bit better.

Option Explicit
Sub ExtractData()
Dim lngI As Long, lngJ As Long, lngK As Long
Dim strAdd1 As String, strAdd2 As String, strAdd3 As String
Dim strAdd4 As String, strAdd5 As String, strAdd6 As String

'Set up variables, assume header row on each sheet
lngI = 2
lngJ = 2
lngK = 2
strAdd3 = "A" & lngI
strAdd4 = "A" & lngJ
strAdd5 = "A" & lngK

'Assume macro initiated from Sheet1 and no blank entries until end of ColA
Do Until Range(strAdd3).Value = ""
    strAdd1 = "G" & lngI
    strAdd2 = "AL" & lngI
    strAdd6 = "AP" & lngI
    If Range(strAdd1).Value = "Metro" And Range(strAdd2).Value > 2 Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet2").Range(strAdd4)
      lngJ = lngJ + 1
    ElseIf Range(strAdd1).Value = "Remote-1" And Range(strAdd2).Value > 4 Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet2").Range(strAdd4)
      lngJ = lngJ + 1
    ElseIf Range(strAdd1).Value = "Remote-2" And Range(strAdd2).Value > "NBD" Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet2").Range(strAdd4)
      lngJ = lngJ + 1
    End If
    If Range(strAdd1).Value = "Metro" And Range(strAdd6).Value > 4 Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet3").Range(strAdd5)
      lngK = lngK + 1
    ElseIf Range(strAdd1).Value = "Remote-1" And Range(strAdd6).Value > 6 Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet3").Range(strAdd5)
      lngK = lngK + 1
    ElseIf Range(strAdd1).Value = "Remote-2" And Range(strAdd6).Value > "NBD" Then
      Range(strAdd3).EntireRow.Copy Sheets("Sheet3").Range(strAdd5)
      lngK = lngK + 1
    End If

    lngI = lngI + 1
    strAdd3 = "A" & lngI
    strAdd4 = "A" & lngJ
    strAdd5 = "A" & lngK
Loop

End Sub

Please note, I have not tested this fully but the mini test that I did seemed to give what I imagined you might have wanted.
Regards
Harry

Hello Anu,
Only saw your post after I posted mine.  There might need to be a conversion to decimal hours for the time cell selection.  That can be done by multiplying by 24.
This can be done in the macro thus:
If Range(strAdd1).Value = "Metro" And Range(strAdd2).Value * 24 > 2 Then .... etc
BUT: This causes a problem when the value is non-numeric eg "NBD" so either you will need some extra logic to handle the non-numeric cases or set those cells to a numeric value that can be tested but does not interfere with your other tests.
The other alternative would be to enter the times as decimal hours and leave the code as it is.
Regards
Harry

hi haarry.

Pls find the sample file Sheet One is base Data .
 this macro is not working on this data .

Sample File-2.zip

thanks
Anu

Hello Anu,

anu_copy_a.zip

First off the data columns in the file were different to those in your first post.  Secondly, there is the potential problem that I mentioned with the possibility of mixed times and strings in the comparison.  Thirdly, the second and third sheets were not named Sheet2 and Sheet3 as in your original post. 
However, I have modified the code to work with the layout and data in your file but the macro will still crash if it strikes a string in the  Response Time and Down Time columns.
Regards
Harry

hi harry ,
 thank you .. it`s working Perfectly .
 in same way i need

-if If Loction Remote-1 And Rt >4hs
3 iF location Remote -2 And Rt >NBD

And Sheet 3 I need 

If Location Remote1 And Dt >6Hrs
If Remote-2 And Dt >NBD 

Thanks
Anu

hi harry ,

it`s working .. thank you very much ..you are always a help.

Anu