Macro Sumif

slamcity

New Member
Joined
Jul 17, 2006
Messages
19
I am having a problem when I run a macro with the Sumif equation in certain cells changing. The macro that I run copies data from one sheet, pastes it to another and manipulates the data (deletes columns and rows and adds a column from which one of the cells on the macro page is referenced).

For some reason, every time the macro is run, the cell reference moves one column over (for instance, if original reference is F:F, the equation changes to E:E every time macro is run).

I have already tried to absolutel reference these column references with no luck.

If anyone can think of anything to help, it would be much appreciated. Thanks a lot!! :biggrin:
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Without seeing your code it is a bit hard to tell, but do you take a copy of the function after you have deleted the column? And is the deleted column before column F?


Tony
 

slamcity

New Member
Joined
Jul 17, 2006
Messages
19
Here is a copy of the code for the macro:

Application.ScreenUpdating = False

Sheets("ORIGINAL").Select
Cells.Select
Selection.Copy
Sheets("Inv Adjs").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Inv Adjs").Select
Rows("1:3").Delete Shift:=xlUp
Columns("A:A").Delete Shift:=xlToLeft
Rows("2:2").Delete Shift:=xlUp
Range("G1").EntireColumn.Insert
Range("g1").Select
ActiveCell.FormulaR1C1 = "ABS"
Range("G2").FormulaR1C1 = "=ABS(RC[-1])"
Range("G2").AutoFill Destination:=Range("G2:G924")
Cells.Select

Sheets("Net & Gross Adjs").Select
Application.ScreenUpdating = True


End Sub

Now here is a copy of the Sumif equation from the other sheet which references info from the Inv Adjs sheet:

=SUMIF('Inv Adjs Net & Gross'!$I:$I,"=us03",'Inv Adjs Net & Gross'!$D:$D)

- The column reference that changes every time the macro is run is "D" in this case, but the "I" column doesn't change at all.
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Ran the code and it didn't seem to change the formula in any way.

Is there more code involved?


Tony
 

Forum statistics

Threads
1,136,702
Messages
5,677,290
Members
419,684
Latest member
BOB101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top