AlwaysLearning2018
Board Regular
- Joined
- Nov 23, 2018
- Messages
- 51
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 = "+"
[TABLE="width: 778"]
<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>[TR]
[TD="width: 110, bgcolor: transparent"]Settlement Date[/TD]
[TD="width: 62, bgcolor: transparent"]Portfolio[/TD]
[TD="width: 190, bgcolor: transparent"]Bank Account Number (Long)[/TD]
[TD="width: 82, bgcolor: transparent"]Long Units[/TD]
[TD="width: 95, bgcolor: transparent"]Long Currency[/TD]
[TD="width: 194, bgcolor: transparent"]Bank Account Number (Short)[/TD]
[TD="width: 75, bgcolor: transparent"]Short Units[/TD]
[TD="width: 99, bgcolor: transparent"]Short Currency[/TD]
[TD="width: 128, bgcolor: transparent"]Cash Plus or Minus[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/30/2019[/TD]
[TD="bgcolor: transparent, align: right"]4444[/TD]
[TD="bgcolor: transparent"]111111USD[/TD]
[TD="bgcolor: transparent, align: right"]450[/TD]
[TD="bgcolor: transparent"]USD[/TD]
[TD="bgcolor: transparent"]111111HKD[/TD]
[TD="bgcolor: transparent, align: right"]0.00[/TD]
[TD="bgcolor: transparent"]HKD[/TD]
[TD="bgcolor: transparent"]+[/TD]
[/TR]
</tbody>[/TABLE]
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 = "+"
[TABLE="width: 778"]
<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>[TR]
[TD="width: 110, bgcolor: transparent"]Settlement Date[/TD]
[TD="width: 62, bgcolor: transparent"]Portfolio[/TD]
[TD="width: 190, bgcolor: transparent"]Bank Account Number (Long)[/TD]
[TD="width: 82, bgcolor: transparent"]Long Units[/TD]
[TD="width: 95, bgcolor: transparent"]Long Currency[/TD]
[TD="width: 194, bgcolor: transparent"]Bank Account Number (Short)[/TD]
[TD="width: 75, bgcolor: transparent"]Short Units[/TD]
[TD="width: 99, bgcolor: transparent"]Short Currency[/TD]
[TD="width: 128, bgcolor: transparent"]Cash Plus or Minus[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4/30/2019[/TD]
[TD="bgcolor: transparent, align: right"]4444[/TD]
[TD="bgcolor: transparent"]111111USD[/TD]
[TD="bgcolor: transparent, align: right"]450[/TD]
[TD="bgcolor: transparent"]USD[/TD]
[TD="bgcolor: transparent"]111111HKD[/TD]
[TD="bgcolor: transparent, align: right"]0.00[/TD]
[TD="bgcolor: transparent"]HKD[/TD]
[TD="bgcolor: transparent"]+[/TD]
[/TR]
</tbody>[/TABLE]