VBA code to loop and copy column values

[PB7]

Hello all,

I need to loop through a column and copy paste the text string contents 24 columns to the right in this spreadsheet.

This code loops, but I can not figure out how to copy/paste the exact value in the cell in column B.

If a cell in column B happens to be ABC, I need that value copied 24 columns to the right.

ThaSub CopyColumn()
Dim c As Range
' Get Number of Lines and star scaning Row by Row
nlines = ActiveSheet.Range("A5").End(xlDown).Row
n_of_desc = 0
For i = 5 To nlines
Range("B" & i).Select
ActiveCell.Offset(0, 24).Value = ("B" & i).value
Next i
End Sub

Thanks to anyone for any help here.


VBA DOES NOT LIKE: ("B" & i).value for whatever reason!?

 

[sswcharlie]

Hi Peter

Good learning experience for me.

Just some points:

Macro does not pick up any data in row 1. (this is ok I can be happy with that.)

With the first entry for search - can I put in say: font.bold and it selects any row with bold text or any type of font selected ?


Regards

Charlie

 

[Peter_SSs]

Macro does not pick up any data in row 1. (this is ok I can be happy with that.)

That's correct. The code uses AutoFilter to find the data and AutoFilter requires a heading row. You could either
a) permanently insert a heading row, or
b) the code could, each time it is run
- insert a heading row
- do the filter/copy
- remove the heading row

With the first entry for search - can I put in say: font.bold and it selects any row with bold text or any type of font selected ?

No, not with this sort of code. Trying to find and copy based on formatting would be considerably more difficult and would require a different code approach.

 

[sswcharlie]

Hi Peter

Thanks for the info. I keep the code as is. I dont normally use row 1 much at all. So problem resolved.


The font problem I wwill forget about.

Thanks

Charlie