Multi-functional macro???

[mfisher316]

Here's what I need to do:
I have data in multiple rows and 12 columns. When a cell in column B is blank, I need to insert a row at the row below the blank cell. In the inserted row, I need to insert a formula in column c. (formula is done)
Also, when the cells in columns K&L are > 0, I need to cut the cell in column K and paste to same row in column G. I need to cut the cell in column L and paste to same row in column J.

Another thing, we put data in at different times and I don't want to keep running on the same data. (i.e. I only want it to run on data when there are values in columns K&L).

I have a macro that works in Lotus 123 and I need to get it to work in excel (or else I am doomed to continue working in Lotus!).

Please help.

 

[Emanresux]

Hello,

Do you have a working macro in Lotus ? or do you need a macro written from scratch ?

If you have the lotus macro then perhaps you could post it and i could have a go putting it into VBA (i've never worked with Lotus though! It might be interesting to try)

Otherwise if you're up on VBA then i'd suggest writing a macro which will do each of the tasks you want and then control their excecution with IF statments from a Main macro. For thing that you don't know how to do, for example insert a row, you can try using the macro recorder and then inspect the recorded code.

 

[mfisher316]

I can get the columns to cut and paste by using a "counter" loop; however, what I really want is for the loop to start at the current cell and proceed from there. That way, when I enter new data below and rerun the macro, the old columns won't be erased when it re-cuts&pastes.

I don't know how much it will help but here is the macro from Lotus it is all based off current cell location (hence all of the Lefts, Rights, Downs Etc)

\M {END}{R}/RF,~{END}{D}{L}{L}{L}{L}{L}{L}~{END}{L}
{R}{R}{up}@VLOOKUP(+{L}{L}{D},COSTCODES,1)~{d}{END}{L}
{END}{R}/M{END}{D}~{L}{L}~
{L}/M{END}{D}~{L}{L}{L}{L}~
{UP}{END}{L}{END}{D}{END}{END}{D}/WIR~
@VLOOKUP(+{L}{L}{D},COSTCODES,1)~
\Q {END}{D}{D}{D}{IF @CELLPOINTER("type")="b"}{BRANCH \Y}
{IF @CELLPOINTER("type"=""I"}/WIR~@VLOOKUP(+{L}{L}{D},COSTCODES,1)~{branch \Q}
\Y {R}{R}{R}{R}{EDIT}{HOME}@ROUND({END},2)~{R}{R}{R}{EDIT}{HOME}@ROUND({END},2)~{QUIT}

FYI "/wir" is the lotus keyboard command for "insert row" which would correlate to: "Selection.EntireRow.Insert" I guess?

Basically here is the deal, we have the following columns:
"Costcode" "Item#" "Description" "QTY" "Price" "Price Multiplier" "Price Extension" "Labor" "Labor Multiplier" "Labor Extension" "Price with Subtotal" "Labor with Subtotal".

I need to replace "Price Extension" with "Price with Subtotal" and "Labor Extension" with "Labor with Subtotal". And then also insert columns at each instance where the value in "Item#" is zero (blank) in the row below the blank cell.

The VLOOKUP Formula is pretty much the exact same but I have it Reference the specific range in the costcode sheet in excel so that is no problem. If only I could get the rows to insert at the correct spot...

Thanks in advance. I'm very new to VB and found the "help menu" to be seriously lacking.

FYI: Here's the "counter" loop I wrote to cut and paste the columns:

For Counter = 4 To 100
Set curcell = Worksheets("Printouts").Cells(Counter, 1)
If Abs(curcell.Offset(0, 10)) > 0 Then
curcell.Offset(0, 10).Select
Selection.Cut
End If

ActiveCell.Offset(0, -4).Select
ActiveSheet.Paste

If Abs(curcell.Offset(0, 11)) > 0 Then
curcell.Offset(0, 11).Select
Selection.Cut
End If

ActiveCell.Offset(0, -2).Select
ActiveSheet.Paste