coding is not working when inserting row or column

Shweta

Shweta

Well-known Member
Joined
Jun 5, 2011
Messages
514
Hi All,

I am facing a serious problem with my spreadsheet

I have coded for all the columns from column A to column Z. But when I am inserting any column in between then my values are getting transferred to the next column but coding is remaining for the same.

Is there any solution for it?

Please help me out..

Thanks
Shweta
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
"Is there any solution for it?"
Yes. But just how long and/or difficult it might be to employ depends on your code and what all it's doing.

Can you post the code you've got?
 
Upvote 0
Thanks HalfAce,

My data is
D E F
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: #7f7f7f" width=64 height=34>Emp </TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #7f7f7f" width=64>BPH</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #7f7f7f" width=64>BPH Rank</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>32851</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">29.01</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>32833</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">29.73</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>34981</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">27.38</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">4</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31052</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">28.34</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31035</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">32.31</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31099</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">28.30</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>32824</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">45.15</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">3.5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31056</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">29.13</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>32860</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">29.64</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31010</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">27.26</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31031</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">35.98</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>31051</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">30.00</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">5</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>32845</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">25.72</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: yellow">2</TD></TR></TBODY></TABLE>

coding is

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 2 To Sheet1.Range("a2").End(xlDown).Row
If Sheet1.Range("E" & i).Value >= 27 Then
Sheet1.Range("F" & i).Value = 5
ElseIf Sheet1.Range("E" & i).Value < 27 And Sheet1.Range("E" & i).Value >= 26 Then
Sheet1.Range("F" & i).Value = 4.5
ElseIf Sheet1.Range("E" & i).Value < 26 And Sheet1.Range("E" & i).Value >= 25 Then
Sheet1.Range("F" & i).Value = 4
ElseIf Sheet1.Range("E" & i).Value < 25 And Sheet1.Range("E" & i).Value >= 24 Then
Sheet1.Range("F" & i).Value = 3.5
ElseIf Sheet1.Range("E" & i).Value < 24 And Sheet1.Range("E" & i).Value >= 23.5 Then
Sheet1.Range("F" & i).Value = 3
ElseIf Sheet1.Range("E" & i).Value < 23.5 And Sheet1.Range("E" & i).Value >= 23 Then
Sheet1.Range("F" & i).Value = 2
ElseIf Sheet1.Range("E" & i).Value < 23 And Sheet1.Range("E" & i).Value >= 22 Then
Sheet1.Range("F" & i).Value = 1
ElseIf Sheet1.Range("E" & i).Value < 22 Then Sheet1.Range("F" & i).Value = 1
End If
Next
End Sub

Now the problem is if I am inserting a column between E and F, the data of column F is getting shifted to column G, but the coding is remaining for the column F. I have to change the coding manually whenever I want to insert any column to it.
 
Upvote 0
Here's what I would try first.
Give column F a defined range name.
(Highlight the entire column F and from the menu: Insert > Name > Define.
Make sure the 'Refers to' field says Sheet1!$F:$F and then give it whatever name you like. (In this example I named it ResultsColumn). Then hit OK.

Then you can refer to your named range in the code, like so:
Code: 
Private Sub CommandButton1_Click()
Dim i As Long

With Sheet1
  For i = 2 To Sheet1.Range("a2").End(xlDown).Row
  RCol = .Range("ResultsColumn").Column
    If .Range("E" & i).Value >= 27 Then
      .Cells(i, RCol).Value = 5
    ElseIf .Range("E" & i).Value < 27 And .Range("E" & i).Value >= 26 Then
      .Cells(i, RCol).Value = 4.5
    ElseIf .Range("E" & i).Value < 26 And .Range("E" & i).Value >= 25 Then
      .Cells(i, RCol).Value = 4
    ElseIf .Range("E" & i).Value < 25 And .Range("E" & i).Value >= 24 Then
      .Cells(i, RCol).Value = 3.5
    ElseIf .Range("E" & i).Value < 24 And .Range("E" & i).Value >= 23.5 Then
      .Cells(i, RCol).Value = 3
    ElseIf .Range("E" & i).Value < 23.5 And .Range("E" & i).Value >= 23 Then
      .Cells(i, RCol).Value = 2
    ElseIf .Range("E" & i).Value < 23 And .Range("E" & i).Value >= 22 Then
      .Cells(i, RCol).Value = 1
    ElseIf .Range("E" & i).Value < 22 Then .Cells(i, RCol).Value = 1
    End If
  Next
End With

End Sub

Notice a couple small tweaks I made also.
One is to change the data type for i from Integer to Long. You're dealing with rows here and if you're POSITIVE you'll NEVER be working with a row of 32,767 or higher, Integer will work just fine, but either way when dealing with rows the data type should always be a long, if only to maintain good coding habits.
Two, I put the entire process into a With statement for Sheet1. This way we can refer to Sheet1 one time and not have to do it within each statement.

The beauty of the named range is that the name moves along with the column when columns are added or deleted so you don't have to keep changing your code each time you do one or the other.

Does that help?
 
Upvote 0
Thanx Halface but this is not working, it is showing error message

Error Message: Run Time Errior : "1004"
Application defined or Object defined Error

My coding is:

Private Sub CommandButton1_Click()
Dim i As Long
With Sheet1
For i = 2 To Sheet1.Range("a2").End(xlDown).Row
On Error GoTo 0
rcol = .Range("ResultsColumn").Column
If Application.WorksheetFunction.IsError(.Range("E" & i).Value) Then
.Cells(i, rcol).Value = .Range("E" & i).Value
ElseIf .Range("E" & i).Value >= 27 Then
.Cells(i, rcol).Value = 5
ElseIf .Range("E" & i).Value < 27 And .Range("E" & i).Value >= 26 Then
.Cells(i, rcol).Value = 4.5
ElseIf .Range("E" & i).Value < 26 And .Range("E" & i).Value >= 25 Then
.Cells(i, rcol).Value = 4
ElseIf .Range("E" & i).Value < 25 And .Range("E" & i).Value >= 24 Then
.Cells(i, rcol).Value = 3.5
ElseIf .Range("E" & i).Value < 24 And .Range("E" & i).Value >= 23.5 Then
.Cells(i, rcol).Value = 3
ElseIf .Range("E" & i).Value < 23.5 And .Range("E" & i).Value >= 23 Then
.Cells(i, rcol).Value = 2
ElseIf .Range("E" & i).Value < 23 And .Range("E" & i).Value >= 22 Then
.Cells(i, rcol).Value = 1
ElseIf .Range("E" & i).Value < 22 Then .Cells(i, rcol).Value = 1
End If
Next
End With
End Sub

Thanks
Shweta
 
Upvote 0
Firstly just a comment. I don't understand the yellow cell values in post #3 - they don't seem to agree to the code requirements. :confused:

Do you really need a macro? Could you just use a formula in column F? F2, copied down:
=LOOKUP(E2,{0,23,23.5,24,25,26,27},{1,2,3,3.5,4,4.5,5})
Then if a new column is added, the formulas will just adjust automatically.

Assuming you do need a macro, then this would be my suggestion. It also just populates the relevant column with a formula but if you want static values instead of formulas, just uncomment the .Value = .Value line of code.

I have also assumed that you want those final values to always be based on the 'BPH' column.
If they should be based on whatever is immediately to the left of the column they are being entered into, then the code would be simpler.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> LastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, BPHCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, BPHRankCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> oSet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><br>    <SPAN style="color:#00007F">Const</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=LOOKUP(RC[#]," _<br>        & "{0,23,23.5,24,25,26,27},{1,2,3,3.5,4,4.5,5})"<br><br>    LastRow = Range("A2").End(xlDown).Row<br>    BPHCol = Rows(1).Find(What:="BPH", LookIn:=xlValues, _<br>        LookAt:=xlWhole, MatchCase:=False, _<br>        SearchFormat:=False).Column<br>    BPHRankCol = Rows(1).Find(What:="BPH Rank", LookIn:=xlValues, _<br>        LookAt:=xlWhole, MatchCase:=False, _<br>        SearchFormat:=False).Column<br>    oSet = BPHCol - BPHRankCol<br><br>    <SPAN style="color:#00007F">With</SPAN> Cells(2, BPHRankCol).Resize(LastRow - 1)<br>        .FormulaR1C1 = Replace(f, "#", oSet)<br><SPAN style="color:#007F00">'        .Value = .Value</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
VBA Code to activate the tab and and transfer data
Replies
1
Views
250
Joe4
Joe4
J
  • Question Question
VBA or formula to move data in last used column to column A
Replies
5
Views
180
Peter_SSs
Peter_SSs
Q
  • Solved
Excel existing code flaw
2
Replies
11
Views
237
MARK858
MARK858
N
  • Solved
Find next visible cell, add a formula and then fill column down
Replies
8
Views
401
MARK858
MARK858
W
  • Solved
Insert column using VBA
Replies
5
Views
191
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,527
Messages
6,179,348
Members
452,907
Latest member
Roland Deschain

Share this page

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
Back
Top