Macro - VBA

[sitelbanat]

how would I write a macro that says

IF column A sheet 1 = "Bank Account Number"
then copy the data in that same row from column C and H to paste them in sheet 2 column A and B

 

[Smitty]

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(Rows.Count, "A").End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c = "Bank Account Number" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
                <SPAN style="color:#00007F">With</SPAN> LastRow
                    .Offset(1) = c.Offset(, 2)
                    .Offset(1, 1) = c.Offset(, 7)
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

 

[HalfAce]

Hello sitelbanat,
You didn't specify if you want these values to be placed in sheet2 on the same
row(s) they come from on sheet1 (which could be done using formulas instead of vba)
or if you want them just listed on sheet2 from row 2 on down.
Below is a way to do either.
First one will place them in the same row(s) they are found in on sheet1.

Sub Demo()
Dim LstRw As Long, ThisRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For ThisRw = 1 To LstRw
  If Cells(ThisRw, "A").Value = "Bank Account Number" Then
    Sheets("Sheet2").Cells(ThisRw, "A").Value = Cells(ThisRw, "C").Value
    Sheets("Sheet2").Cells(ThisRw, "B").Value = Cells(ThisRw, "H").Value
  End If
Next ThisRw

End Sub

Second one will just list them out in sheet2 starting in row 2.

Sub Demo2()
Dim LstRw As Long, ThisRw As Long
LstRw = Cells(Rows.Count, "A").End(xlUp).Row

For ThisRw = 1 To LstRw
  If Cells(ThisRw, "A").Value = "Bank Account Number" Then
    Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)(2).Value = Cells(ThisRw, "C").Value
    Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)(2).Value = Cells(ThisRw, "H").Value
  End If
Next ThisRw

End Sub

These are both intended to be run from sheet1.


[EDIT:]
Hey Smitty, how's things?
Been away for a while. (On vacation.)
Happy to report the moose is in the freezer!
Looks like we get to eat again this winter.

 

[sitelbanat]

Hello HalFace

The first macro did work but it only covers the first set of data on the sheet. How would i change it to go through all of the data in the sheet.

every set of data has the account number, amount and the activity. I need the macro to capture the account number and amount then paste them in sheet 2. I have 80 Accounts.

 

[sitelbanat]

ADVERTISEMENT

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> foo()
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range
    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> Range
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(Rows.Count, "A").End(xlUp))
            <SPAN style="color:#00007F">If</SPAN> c = "Bank Account Number" <SPAN style="color:#00007F">Then</SPAN>
                <SPAN style="color:#00007F">Set</SPAN> LastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp)
                <SPAN style="color:#00007F">With</SPAN> LastRow
                    .Offset(1) = c.Offset(, 2)
                    .Offset(1, 1) = c.Offset(, 7)
                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

Smitty

This didnt work

 

[Smitty]

This didnt work

That doesn't tell me much.

Smitty

 

[sitelbanat]

i run the macro and nothing happend, let me explain again

In sheet1 it contains account number, account balance, and activity of 80 different accounts. i need the macro to go through all that data and copy the date, account number, and account balance then paste it in sheet2. placing the date in column A, account number in column B, and amount in column C. listing all 80 accounts.

 

[HalfAce]

The first macro did work but it only covers the first set of data on the sheet. How would i change it to go through all of the data in the sheet.

The code in the first routine I posted is being told to go through each cell in column A (from row 1 down to the last row with data in column A) and check to see if the value in each cell is the text "Bank Account Number".
If that condition is true then it will copy (just the value) of column C from that row to column A in the same row number on sheet 2, and copy the value from column H from that row to column B in the same row number on sheet 2. This is working for me as intended.
What results are you getting exactly?
Is the text "Bank Account Number" found in column A of all rows it's found in?