Find cell, then select data in that column and find & replace

[lilmisspink07]

Hello,

Hopefully someone can help me with this as it has been driving me nuts!

I am writing a macro to format a file. The number of rows can change and also, there may not always be data in every cell so I need to make sure this is covered in the macro.

I have this little extract that I use earlier when auto-filling to ensure I get all the rows:

' Dynamic autofill success counts for all samples
LastRow = Range("A65536").End(xlUp).Row
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault

Now in a later part, I need to find a cell in the header row, then select the column down to the last row and perform a find and replace. I can't figure out how to get my LastRow variable into the selected range though?

This is where it needs to go....

' GTtoAC on DQ381153 & highlight pink
Cells.Find(What:="DQ381153", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Replace What:="T", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="G", Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

I'm guessing that LastRow needs to go somewhere in the red bit but I'm new to VB and struggling to figure it out!!

Thanks in advance!

 

[Weaver]

You should use

La

stRow = Range("A" & rows.count).End(xlUp).Row

so the code is compatible with later versions of xl.

try:

Sub test()
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("B2").AutoFill Destination:=Range("B2:B" & LastRow), Type:=xlFillDefault
    'Now in a later part, I need to find a cell in the header row, then select the column down to the last row and perform a find and replace. I can't figure out how to get my LastRow variable into the selected range though?
    
    This is where it needs to go....
    
    ' GTtoAC on DQ381153 & highlight pink
    Set rng = Cells.Find(What:="DQ381153", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    With Range(rng, Cells(lr, rng.Column))
        .Replace What:="T", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        .Replace What:="G", Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        With .Interior
            .ColorIndex = 38
            .Pattern = xlSolid
        End With
    End With
End Sub

 

[lilmisspink07]

Thank you for your help! The tip about making sure it works for later versions then 2003 which I am using is great, I didn't even think of that.

However the second bit of code doesn't seem to work right - it doesn't do anything to the column I need formatting (approximately AM1), instead it highlights the whole of column C in pink....

I am trying various things along the lines of the below but it breaks on the line where I define the range as I'm sure I'm not doing it right. Any ideas where I am going wrong??

' GTtoAC on DQ381153 & highlight pink
Set StartCell = Cells.Find(What:="DQ381153", After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Range(StartCell, LastRow).Select
Selection.Replace What:="T", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.Replace What:="G", Replacement:="C", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
With Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
End With

 

[Weaver]

I wouldn't 'select' everything - there's no need, but

Range(StartCell, cells(lastrow,startcell.column)).Select

ought to work

 

[lilmisspink07]

Thank you so much, it worked perfectly!!!!