Need help with excel macro/vba problem.

[calihiker]

Fairly new to VBA and would like some help on a problem with overwriting existing values in a column with a formula only if the value of the cell in the next column (same row) is not blank otherwise leave the pre-existing formula alone. So if the other cell in another column is blank then leave the formula alone. Any suggestions? I was using the formula, = IF( ISERROR(IF(ISBLANK(AB9),R9,AL9)), 0, IF(ISBLANK(AB9),R9,AL9)) to populate the column, but later realized that it overwritten the old formula in in the R column and that's not what I wanted. Thanks in advance.
Andres

 

[Zathras]

... the cell in the next column (same row)...

...IF(ISBLANK(AB9),R9,AL9)...

If you are testing for blank in AB9, does that mean that the formula would go in AA9? (The "next" column from AA is AB)

What is the formula? =R9?

What does column AL have to do with it?

Your request does not make much sense as it is currently stated. Please provide more detail...

 

[calihiker]

Sorry for the ambiquity.

I need to transfer values for each row, from the column AL to column R, ONLY IF the row from Column AB is blank (therefore, i tried something like =IF(ISBLANK(AB9),R9,AL9)... in cell R9. But the problem with this formula is that when AB9 is blank, the existing formula (a sum) in R9 is overwritten and replace and so the sum formula is gone. So if AB9 is blank
I need to keep everything the same in R9 and not overwrite it with the formula =IF(ISBLANK(AB9),R9,AL9)... I hope this makes sense... It sounds like I may need to use some VBA to store the existing formula in R9, but if it is possible to bypass inserting a new formula into R9 if the value in AB9 is blank with a macro that would be great! Thanks.

 

[calihiker]

Also would like to add that column R is the old qtys. and AL is the new qtys. and I would like to update only the cells in Column R that don't have a sum() formula in the cell And have a corresponding 'keycode' (in column AB)...Hope this helps!

 

[Zathras]

It's still not very clear to me. It sounds like you could just use a formula like

  =IF(AB9="",SUM(xxxxxxxxx),AL9)

in cell R9 where

SUM(xxxxxxxx)

is your current formula in cell R9, but I suppose if it were really that simple you could have figured it out for yourself. So what is it that you really need?

Can you post some sample data for three or four lines of data showing what is in each of the three columns R, AB and AL, and then show what you want them to look like?

 

[calihiker]

True, =IF(AB9="",SUM(xxxxxxxxx),AL9) would work if it not for multiple empty lines between groups of data and different number of rows to sum per each groups of rows.

But if I used that formuala how could I automate the totals for that group if each group varies in number of rows to sum?

i.e. first group to sum would be =SUM(R12:R29) then the next sum might have to be =SUM(R40:R55) Is there a way to automatically know how many 'continuous' rows (above) there are to use in a sum formula?

Or is there a way to somehow skip pasting a new formula in a cell that has a specific function in it? i.e. sum()?

here is a sample of what I am working on...

Column R Column AB Column AL

LC Qty Keycode LC Input
12,507 ma09 13333
0 ma10 12
11,863 ma11 12113
0 ma12 0
13,896 ma13 14333
sum(r2:r6)

 

[Zathras]

...how could I automate the totals for that group...

This is the first time you mentioned groups. From your sample data I might guess that groups are based on the first two characters of the code in column AB. Is that right?

If so, you should be able to use formulas to put the group codes in their own column and then use the subtotal feature of Excel to get what you want. (Data / Subtotals...)

 

[calihiker]

No, I need to sum based on another column of data. I would like to keep the pre-existing format of the spreadsheet w/o subtotaling.

 

[Zathras]

Ok, so let's see what we have so far:

...with a formula only if the value of the cell in the next column (same row) is not blank...
combined with a sample display that indicates
...sum(r2:r6)..
(which looks like a subtotal) on a line all by itself. And
...keep the pre-existing format of the spreadsheet w/o subtotaling...

As originally posted, you want to have
...overwriting existing values in a column with a formula...
but I still haven't seen an example of the formula that you want, except for a subtotal-like formula which you don't want.

Perhaps you should start over and post your request in a new thread to allow someone else a chance to help you. I'm afraid I can't do much with what you have given me. Sorry.

 

[calihiker]

Thanks anyway, I appreciate your responses.