Macro - VBA

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
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
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
Code:
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.
Code:
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! :cool:
Looks like we get to eat again this winter. :p
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
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

Board Regular
Joined
Oct 17, 2005
Messages
152

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
 

sitelbanat

Board Regular
Joined
Oct 17, 2005
Messages
152
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,114,398
Messages
5,547,729
Members
410,810
Latest member
lilwayne
Top