VBA Run-time error '1004'

[AlwaysLearning2018]

Hi All,

I have a spreadsheet with 2 macros and 2 sheets within the workbook (sheet 1 and sheet 2). I am very junior with VBA so apologies in advance. I have the below in which I am receiving this run-time error and the "If Range("D2").Offset(num - 1, 0).Value > 0 Then" row is highlighted yellow for this error. Also below are my headers and data in my sheet. I believe the problem is I am basically trying to run an identical macro in sheet 2 that I am trying to run in sheet 1, but with a few modifications (in the below in macro 2, I am working with range "G2" instead and the offset would be .Offset(num -1, 2).Value = "-").

Is there a way in the below variable to first activate the sheet? I've been trying to figure out on my own and read about activating the sheet you are currently working with because excel doesn't know your object.

Any help would be GREATLY appreciated!!! Thank you.



Dim nums As Long, num As Long
nums = Range("D2").End(xlDown).Row
For num = 1 To nums
If Range("D2").Offset(num - 1, 0).Value > 0 Then
Range("D2").Offset(num - 1, 5).Value = "+"

Settlement Date	Portfolio	Bank Account Number (Long)	Long Units	Long Currency	Bank Account Number (Short)	Short Units	Short Currency	Cash Plus or Minus
4/30/2019	4444	111111USD	450	USD	111111HKD	0.00	HKD	+

<colgroup><col width="110" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4022;"> <col width="62" style="width: 47pt; mso-width-source: userset; mso-width-alt: 2267;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="82" style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;"> <col width="99" style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <tbody> </tbody>

 

[JustynaMK]

Hi, maybe try something like that:

Sub foo()
    Dim nums As Long, num As Long


    With Sheet2
        nums = .Range("D2").End(xlDown).Row
        For num = 1 To nums
            If .Range("D2").Offset(num - 1, 0).Value > 0 Then
                .Range("D2").Offset(num - 1, 5).Value = "+"
            End If
        Next num
    End With
End Sub

Using "With Sheet2" statement should do the trick.

 

[AlwaysLearning2018]

Hi JustynaMK,

I think I found a better explanation of what is happening. If I have more than 1 row of data, the code I have works perfectly. I have other coding in the same macro that deletes out data I don't need and when I'm left with a single row of "good" data I get this run time error. I tried adding the With Sheet2 statement but still an into the same error. Very possible I just am not implementing your code correctly. Any suggestions would be great, and thank you SO much for taking the time to try to assist me. Very grateful for that.

 

[JustynaMK]

Hi,

Thanks for providing more info! If you are left with only a single row of "good" data, then nums argument will present a value of 1048576 (max row in Excel), causing the Run-time error.

nums = .Range("D2").End(xlDown).Row

Try replacing it with a different method of finding the last row, for example:

nums = .Range("D" & .Rows.Count).End(xlUp).Row

 

[AlwaysLearning2018]

Hi,
That makes sense as to why I am getting this error! I attempted to use the below code (using yoursuggesting of changing the last row method) and received a Compile error:Invalid or unqualified reference. Anysuggestions? Full code below. Again, thank you so very much for taking the time!!!


Dim nums As Long, num As Long
nums = .Range("D" &Rows.Count).End(xlDown).Row
For num = 1 To nums
If.Range("D").Offset(num - 1, 0).Value > 0 Then
.Range("D").Offset(num - 1, 5).Value = "+"

End If
Next num

 

[JustynaMK]

Cool, glad it worked for you!
As for your code, there are a few small issues.

1) You are missing "With Sheet2 ... End With" statement. Your code is using references like:

nums = .Range("D" &Rows.Count).End(xlDown).Row

Because you didn't specify to which Sheet a given Range should be assigned, VBA doesn't know how to interpret .Range (notice the dot before "Range"). Of course, you can use a different sheet code/name if needed (e.g. With Sheet1, With Sheets("mysheet"), etc.)

You can use "With...End With" statement to simplify your code, so instead of having:

    Sheet2.Range("A1").Value = "test1"
    Sheet2.Range("B1").Value = "test2"
    Sheet2.Range("C1").Value = "test3"
    Sheet2.Range("D1").Value = "test4"

...you can make it a little bit nicer by using With:

    With Sheet2
        .Range("A1").Value = "test1"
        .Range("B1").Value = "test2"
        .Range("C1").Value = "test3"
        .Range("D1").Value = "test4"
    End With

2) Your nums statement is incorrect:

nums = .Range("D" &Rows.Count).End(xlDown).Row

Apart from missing Sheet2 reference (as per 1st point), you need to also add "dot" to .Rows.Count and replace xlDown with xlUp. Otherwise you are going to end up with the same issue as before ('run-time error') in case you are having one-row data set.

3) You need to change "D" to "D2" in the code below (and add space after the first "If") :

If.Range("D").Offset(num - 1, 0).Value > 0 Then
.Range("D").Offset(num - 1, 5).Value = "+"

So, to summarise, your final code might look somewhat like that:

Sub foo2()
    Dim nums As Long, num As Long
    
    With Sheet2
        nums = .Range("D" & .Rows.Count).End(xlUp).Row
        For num = 1 To nums
            If .Range("D2").Offset(num - 1, 0).Value > 0 Then
                .Range("D2").Offset(num - 1, 5).Value = "+"
            End If
        Next num
    End With
End Sub

 

[AlwaysLearning2018]

Hi,

Still haven't had success. I really really appreciate your assistance. I'm just not getting it. I'm sorry! My result now is I don't get the "+" to populate in the column. I don't get the run time error with the final code you provided but again now don't get the "+" to generate in the appropriate column. That column is just blank now.

 

[JustynaMK]

Any chance you could post the table with some sample data or publicly share the file? It might be easier for me to notice the issue.

Alternatively, if all you want is to simply identify positive values in column D and display "+" symbol in column I, why not using an Excel formula like:
=IF(D2>0,"+","")

Best regards,
Justyna

 

[AlwaysLearning2018]

Hi Justyna,

Sorry, your code works PERFECTLY!! I figured out my issue. I was simply referencing the incorrect sheet number. Fixed that. This is AMAZING and I thank you very much for all of your help!!!!

 

[JustynaMK]

Uff! Awesome I'm happy you figured it out. Take care.