Automatically select the last column with data - 2 Back columns

[sofas]

Hello...Please correct the code, I have a datasheet whose number of columns is not fixed, it could be 3, 5, or maybe 10, I want to select the last column always - 2 columns back so that I can execute the following code

Sub Copy_n_Paste()
Dim wsDest As Worksheet: Set wsDest = sheet3

Dim lr As Long, Lastcol As Long

Dim SrchRng As Range, cel As Range

lr = wsDest.Cells(Rows.Count, "a").End(xlUp).Row - 1

Lastcol = wsDest.Cells(5, wsDest.Columns.Count).End(xlToLeft).Column
  
Set SrchRng = wsDest.Cells(5, Lastcol - 2 & lr)
For Each cel In SrchRng
    If cel.Value <> "" Then
    cel.Offset(1, 1).Value = cel.Value
    cel.Offset(1, 1).NumberFormat = "###0.00"
    
    
    End If
Next cel
        

End Sub

 

[MARK858]

As a bit of a guess what you want...

Set SrchRng = wsDest.Range(wsDest.Cells(5, Lastcol - 2), wsDest.Cells(lr, Lastcol - 2))

 

[sofas]

As a bit of a guess what you want...

Set SrchRng = wsDest.Range(wsDest.Cells(5, Lastcol - 2), wsDest.Cells(lr, Lastcol - 2))

Very cool. Thank you, this is what I was looking for.

Can I find a faster code than it that works, but it's rather slow?

Sub Copy_n_Paste()
Dim wsDest As Worksheet: Set wsDest = sheet3

Dim lr As Long, Lastcol As Long

Dim SrchRng As Range, cel As Range

lr = wsDest.Cells(Rows.Count, "a").End(xlUp).Row - 1

Lastcol = wsDest.Cells(5, wsDest.Columns.Count).End(xlToLeft).Column
Set SrchRng = wsDest.Range(wsDest.Cells(6, Lastcol - 2), wsDest.Cells(lr, Lastcol - 2))
For Each cel In SrchRng
    If cel.Value <> "" Then
    cel.Offset(1, 1).Value = cel.Value
    cel.Offset(1, 1).NumberFormat = "###0.00"
    'cel.Offset(1, 1).NumberFormat = "#,##0.00"
    cel.Offset(1, 1).Font.Bold = True
    cel.Offset(1, 1).Font.Color = vbRed
    cel.Offset(1, 1).EntireColumn.AutoFit
    
    End If
Next cel
    
End Sub

 

[MARK858]

Very cool. Thank you, this is what I was looking for.

You're welcome

Can I find a faster code than it that works, but it's rather slow?

That is a different question, which is unrelated to your original question and so you will need to start a new thread