VBA Macro

J

Jeff

Hello,

I have the following workbook with column A and B:
A B
199 100.00
200 50.00
201 100.00
201 200.00
199 60.00
I need help designing a VBA macro that would:
1 - Create a new worksheet in the workbook and extract column A & B for all
the "199" in that new worksheet
2 - Insert "1" in column "C" if the value in a row in column "A" is
different than the value above that row.
Thanks,

2 -
 
D

Debra Dalgleish

You can record a macro as you do the steps manually.
Insert a new sheet
Use an Advanced Filter to extract the data.
Use an IF formula in column C to compare the value in
column A to the value in the cell above
Copy the formula down to the last row of data
 
J

Jeff

Thank you.

However, because the size of my workbook changes, I need a generic macro.
That's why I need a VBA Macro.
Thanks,
 
D

Dave Peterson

After you record the macro, you can generalize it.

so if you get a line that refers to a specific range, like:

activesheet.range("a1:b99")....

you can do something like:

Dim LastRow as long
dim myRng as long

with activesheet
lastrow = .cells(.rows.count,"A").end(xlup).row
set myrng = .range("a1:b" & lastrow)
myrng.....(do something here)
end with

This example uses the last cell in column A to determine the amount of rows
used.

If you have trouble generalizing your code, post back with your specific
question and I'm sure someone will jump in.
 
Top