Macro is not working for the right column

[Shweta]

Hi All,

I have created a macro for column C that is based on column A and column B. It is working properly.

But when I am inserting a column between column B & C then the coding of column C should be moved to column D because I have inserted this column for something else but it is remaining for the same column.

For example:
.range("C"&i).value = .range("A"&i).value + .range("B"&i).value

After inserting the column it should be work like this
.range("D"&i).value = .range("A"&i).value + .range("B"&i).value

without doing any change in coding.

Please help!

Regards,
Shweta

 

[Jonmo1]

without doing any change in coding.

Sorry, but if you want the code to do something different than what it currently does, then you have to change the coding...

There's a few approaches...

Do you have headers in each column?
Column A has "Name" in A1
Column B has "Age" in B1

or similar..
Just something to identify which columns to work with.
We can then do a search for those values in Row 1 to determine the columns.
And it will dynamically adjust when columns are inserted or deleted.

 

[texasalynn]

based off of inserting columns a macro does not change it's placements

 

[Shweta]

Thanks for the quick response!

Yes, I have headings for columns. Column A is SALARY & Column B is INCENTIVE.
In column C I have to add the both. In this case coding is working fine.

coding : .range("C"&i).value = .range("A"&i).value + .range("B"&i).value

Now I insert a column before column A to enter the NAME of the employees. In this case it should be
.range("D"&i).value = .range("B"&i).value + .range("C"&i).value

I think now I am clear.
I need dynamic coding because when I do it with excel it is dynamic, formula automatically moves with the column. But it is not being possible with VBA.

Instead of entering the column name("A","B",...) in the coding, can we use HEADINGS so that we can match the coding with the headings & then when I move columns then coding automatically moves.

Some like that...??

Shweta

 

[Jonmo1]

Yes, Excellent.
But we also need the heading for column C.
for this example, we will say the heading in column C is RESULT

Dim Slry As Long, Inctv As Long, Rslt As Long, HdrRw As Long
 
HdrRw = 1 'Assuming headers are in Row 1
Slry = Application.Match("Salary",Rows(HdrRw),0)
Inctv = Application.Match("Incentive",Rows(HdrRw),0)
Rslt = Application.Match("Result",Rows(HdrRw),0) 'change Result to Header value of Col C
 
Cells(i, Rslt).Value = Cells(i,Slry).Value + Cells(i, Inctv).Value

Hope that helps.

 

[Shweta]

Thanks Jonmo1,

Let me give it a try then I will get back to you.

Thanks again.

Shweta.

 

[Shweta]

Hi Jonmo1,

Sorry to say but this coding is not working
Before running the coding, my data is like:

Col1 Col2 Col3
SALARY INCENTIVE RESULT
20156 1500

After running the coding

Col1 Col2 Col3
SALARY INCENTIVE SALARYINCENTIVE
20156 1500

Please help me out

Shweta

 

[Jonmo1]

Post the code you used. The whole code.

What I posted was just meant as an example to show you how to do it.
Wasn't meant to fit your situation exactly..

 

[Shweta]

I am sorry Jonmo1, I tried it again & its working now

Thanks a lot!

Regards,
Shweta