Strange error

[arehman1289]

I am writing a basic code as:

Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(nr, "D").Formula = "bla"

In this case line 3 isnt executed, but when I change nr in this line to an integer it works as:

Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(43, "D").Formula = "bla"

It is quite strange since the variable nr has been declared globally and if this were the problem with the variable the first two lines wouldnt execute. What could it be ?

Is there anyway to debug it line by line with global and local variables enlisted ?

Thanks

 

[Joe4]

What happens if you add a MsgBox with the value of "nr"?
What does it return?

MsgBox nr
Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(nr, "D").Formula = "bla"

Also, do you have any event procedure code in your workbook ("Worksheet_Change" procedures)?
If so, what does that code look like?

And are you just dealing with one worksheet here, or multiple?

 

[arehman1289]

Yes this is the whole code, which links 3 worksheets, which also have codes:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, nr, k, nr2 As Long

' Exit if multiple cells updating simultaneously
If Target.CountLarge > 1 Then Exit Sub

' Only run if cell B1 updated

If Target.Address = Range("D5").Address Then
' Only run if numeric value entered
If IsNumeric(Target.Value) And (Target.Value > 0) Then
Application.ScreenUpdating = False
Application.EnableEvents = False

' Set default value of next row
nr = 41
nr2 = 19
' Loop through number of times to copy
For i = 1 To (Int(Target.Value) - 1)
' Copy values from A2:B5 to next range

Range("A20:E40").Copy Cells(nr, "A")
Cells(nr, "A").Interior.ColorIndex = 35
Cells(nr, "D").Formula = "bla"

' Increment next start by 4 rows
For k = 1 To 20
If k = 3 Then
Cells(nr + k, "D").Formula = "=VLOOKUP('User Input'!$C" & nr2 + 3 & ",'Work process list'!$A$2:$B$11,2,FALSE)"
End If
If k = 4 Then
Cells(nr + k, "D").Formula = "=3.14*'User Input'!$C" & nr2 + 1 & "8*0.001*'User Input'!$C" & nr2 + 1 & "*0.001/4"
End If
If k = 6 Then
Cells(nr + k, "D").Formula = "=66.4*'User Input'!C" & nr2 + 1 & "*Calculations!D" & nr + 1
End If
If k = 7 Then
Cells(nr + k, "D").Formula = "=IF((0.11*POWER((($D$3/('User Input'!$C" & nr2 + 1 & "))+(68/$D" & nr + 5 & ")),0.25))>0.018,(0.11*POWER((($D$3/('User Input'!C" & nr2 + 1 & "))+(68/D" & nr + 5 & ")),0.25)),(0.85*(0.11*POWER((($D$3/('User Input'!C" & nr2 + 1 & "))+(68/D" & nr + 5 & ")),0.25))+0.0028))"
End If
If k = 8 Then
Cells(nr + k, "D").Formula = "=($D" & nr + 6 & "*'User Input'!C" & nr2 + 2 & "*$D$4*D" & nr + 6 & "*D" & nr + 6 & ")/(2*'User Input'!C" & nr2 + 1 & "/1000)"
End If
If k = 11 Then
Cells(nr + k, "D").Formula = "=$D" & nr + 9 & "/'User Input'!$C" & nr2 + 1 & ""
End If
If k = 13 Then
Cells(nr + k, "D").Formula = "=VLOOKUP('User Input'!C" & nr2 + 5 & ",Tables!$A$35:$K$41,VALUE(MATCH('User Input'!C" & nr2 + 6 & ",Tables!$B$34:$K$34,0))+1,TRUE)"
End If
If k = 14 Then
Cells(nr + k, "D").Formula = "=((D" & nr + 14 & "*$D$4*D" & nr + 2 & "*D" & nr + 2 & ")/2)*'User Input'!C" & nr2 + 4 & ""
End If
If k = 15 Then
Cells(nr + k, "D").Formula = "=INDEX(Tables!$B$27:$H$27, MATCH(MIN(ABS(Tables!$B$27:$H$27-'User Input'!C" & nr2 + 1 & ")),ABS(Tables!$B$27:$H$27-'User Input'!C" & nr2 + 1 & "),0))"
End If
If k = 16 Then
Cells(nr + k, "D").Formula = "=VLOOKUP('User Input'!C" & nr2 + 7 & ",Tables!$A$28:$H$30,VALUE(MATCH($D" & nr + 15 & ",Tables!$B$27:$H$27,0))+1,TRUE)"
End If
If k = 17 Then
Cells(nr + k, "D").Formula = "=(($D" & nr + 16 & "*$D$4*D" & nr + 1 & "*D" & nr + 1 & ")/2)*'User Input'!C" & nr2 + 6 & ""
End If
If k = 18 Then
Cells(nr + k, "D").Formula = "=VLOOKUP('User Input'!C" & nr2 + 9 & ",Tables!$A$18:$M$24,VALUE(MATCH('User Input'!C" & nr2 + 10 & ",Tables!$B$18:$M$18,0))+1,TRUE)"
End If
If k = 19 Then
Cells(nr + k, "D").Formula = "=((D" & nr + 18 & "*$D$4*D" & nr + 1 & "*D" & nr + 1 & ")/2)*'User Input'!C" & nr2 + 8 & ""
End If
Next k
nr = nr + 21
nr2 = nr2 + 12
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End If

End Sub

 

[Joe4]

So what does the Message Box I gave you return in your code?
Do you have any merged cells on your worksheet?

Have you tried placing a breakpoint in this code near the top, so it will stop the code at that step when the procedure is called?
Then, you will be able to debug by using the F8 key, and going through your code line-by-line and watching what happens.
Note that while in break mode, if you hover your cursor over any variable, it will show you that variable's value at that point in time.

 

[arehman1289]

there is no option to insert a breakpoint. probably because its a code not a macro, not sure though ?
I tried renaming all the variables so that they are different from the other sheets.
Yes there are merged cells, does this impact the code ? There arent any on the row the code is being implemented.

 

[Joe4]

You can set a breakpoint in any procedure.
This shows you how to do it: MS Excel 2013: Set Breakpoint in VBA
Note that you have to put it on a line of code that does something. You cannot place it on a comment or a Dim statement.

Yes there are merged cells, does this impact the code ?

I would STRONGLY suggest that you get rid of merged cells. They cause a ton of problems for VBA and things like sorting!
Most programmers avoid using these at all, if they can help it. They lead to nothing but headaches!

If you are simply merging cells across single rows, you can achieve the exact same visual representation without all the issues by using the "Center Across Selection" formatting feature instead.
See here for details: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

 

[Norie]

Where have you actually declared nr 'globally'?

 

[arehman1289]

i unmerged all cells, didnt work so now going for the debugging.

i declared them before the for statement as can be seen in the message above:

Set default value of next row
nr = 41
nr2 = 19

 

[Joe4]

i declared them before the for statement as can be seen in the message above:

Set default value of next row
nr = 41
nr2 = 19

Note that is not what is meant when you say "declare globally".
"Declare globally" means that you have declared the variables OUTSIDE of the procedure, so that they can be used across multiple procedures.

They would be declared ABOVE your first procedure like this:

Dim nr as Long

Private Sub Worksheet_Change(ByVal Target As Range)
     ...
End Sub

I am guessing that you misspoke, and don't really mean that you declared it "globally".

Also, note that this line does not do what you think it does:

Dim i, nr, k, nr2 As Long

In that case, only "n2" is declared to be a Long variable.
All of the others would be set to Variant.

Each variable needs to be declared explicitly, either like this:

Dim i As Long, nr As Long, k As Long, nr2 As Long

or like this:

Dim i As Long
Dim nr As Long
Dim k As Long
Dim nr2 As Long

Note that is not to day that your code won't work because of that - it will, it just defeats the purpose of declaring them in the first place (all undeclared variables are defaulted to "Variant" anyway). The issue is that Variant will accept ANYTHING, while Long will only accept whole numbers up to a certain value. So declaring them helps prevent errors (i.e. making sure someone doesn't enter text in a number field), and also helps to identify typos you may have in your code if you have "Option Explicit" turned on (which forces you to declare all variables before using them in your code).