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!?

 

[sous2817]

Try:


ActiveCell.Offset(0, 24).Value = Range.("B" & i).value

 

[p45cal]

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

in that line, change
("B" & i)
to
Activecell
(but there are quicker and easier ways to copy a column)

 

[PB7]

Thank you both, Sous and P45 for the quick responses here. I think I tried using ActiveCell with no luck (per the 2nd response) here. Let me try both and confirm back here in a few minutes, and again, many thanks.

 

[Peter_SSs]

As p45cal has indicated there are quicker ways. Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyColumn2()<br>    <SPAN style="color:#00007F">With</SPAN> Range("B5:B" & Range("A5").End(xlDown).Row)<br>        .Offset(0, 24).Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>

 

[PB7]

Mr. E VBA colleagues,

The first 2 solutions worked great! And I'll try the 3rd one too. Will try to remember to indent my code too.

As an accountant who just doesn't get VBA, thanks for the merciful help!

 

[sswcharlie]

Hi

With the last option it only copies the whole column across to new column.

How do I get it to copy only the whole row , matching cell A5, with info more than 1 column across to new position and in next available space in the new column.

Cell A5 value = ABC

B1 wer C1 tin
B2 ABC C2 plastic

Result

Say in H1 etc ABC plastic

I would like to have an input box to list the value that is being looked for, instead of using A5 .
Comments would be appreciated.

Charlie

 

[Peter_SSs]

Sorry, mis-read the question.

 

[sswcharlie]

Hi

When I run your code posted on 22 May, it copies all of col B into the new column.

I want to have only those items in the column B with ABC, and post the whole row with that ABC is included into the new postion. (not just 1 column). And when it goes into the new columns it is posted to next available row.

Probably best to get this going first. Then I want to be able to have an input box for user to enter - Ref they are looking for eg ABC, also the column in which it can be found eg B, as well as show the column to start the paste eg Column T.

Hope that is clearer now.


Thanks


Charlie

 

[Peter_SSs]

Test this. If you wanted to copy data that included say "ABC" in some longer text like "I watch ABC" then you can use wildcards for the search string like this:

*ABC for anything ending with ABC.
*ABC* for ABC anywhere in the cell.
??ABC* for exactly 2 characters before the ABC but anything after it.
ABC for exactly ABC in the cell.
etc

If it doesn't do what you want, you may need to provide some more detail about your data and layout.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Copy_Data()<br>    <SPAN style="color:#00007F">Dim</SPAN> Inp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a<br>    <br>    Inp = InputBox("Enter search text, source column & " _<br>        & vbLf & "destination column separated by commas" _<br>        & vbLf & "(eg ABC,B,T)")<br>    a = Split(Inp, ",")<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(a) = 2 <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">With</SPAN> Range(a(1) & 1, Range(a(1) & Rows.Count).End(xlUp))<br>            .AutoFilter Field:=1, Criteria1:=a(0)<br>            .CurrentRegion.Offset(1).Copy _<br>                Destination:=Range(a(2) & Rows.Count).End(xlUp).Offset(1)<br>            .AutoFilter<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        MsgBox "Incorrect input data"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>