Currently, I am referencing a range of cells like this:
Dim LR As Long
With Sheets("Event_Data")
On Error Resume Next
LR = Cells(Rows.Count, "M").End(xlUp).Row
Range("M8:M" & LR).Replace What:=" ", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Sheet1.Range("M8:M" & LR).SpecialCells(xlCellTypeConstants).Copy _
Destination:=Sheet2.Range("B2")
I am now trying to not specifically reference column M because the data I am looking at won't always be in column M. In my data I import to Excel, the column before the data that is in M in this case always has the word "TRIG," so I came up with a way to reference the data without using M...partially. So far I have this code working:
Dim trig As Integer
trig = Application.Match("TRIG", Sheet1.Rows(7), 0)
Dim LR As Long
With Sheets("Event_Data")
On Error Resume Next
LR = Cells(Rows.Count, trig + 1).End(xlUp).Row
Range("M8:M" & LR).Replace What:=" ", Replacement:="", Lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
Sheet1.Range("M8:M" & LR).SpecialCells(xlCellTypeConstants).Copy _
Destination:=Sheet2.Range("B2")
My question is, how do I change
to a format of calling the cells using the trig variable? I have tried a couple of different things, but no luck yet...Thanks for your help!
Bookmarks