What is wrong with this?
 

Hi,

I have a piece of code that isn't working the way I need it to. The
intention is to:
1) unprotect a sheet (working)
2) find the first blank line in column A (working)
3) copy a unique number from another sheet in this cell (working)
4) copy data from a third sheet to the cell immediately to the right of the
one that has just been copied to (not working)

The code I have for this is:

Dim vNewRisk As Variant ' used to find blank cell
Dim rLookUpRange As Range ' range to Vlookup on the
Identification sheet
Dim sRiskNumber As String ' unique identifier of new risk
Dim vTitle As Variant ' used to copy title data from
Identification page

Sheets("Treatment - Controls").Unprotect
Set vNewRisk = Sheets("Treatment - Controls").Range("a8")
Do Until vNewRisk.Value = "" ' look for first
blank cell
Set vNewRisk = vNewRisk.Offset(1, 0)
Loop

sRiskNumber = Sheets("user data").Range("b7")
Set rLookUpRange = Sheets("identification").Range("a:b")
vNewRisk.Value = sRiskNumber ' put new risk
number into first blank line
vTitle = Application.VLookup(sRiskNumber, rLookUpRange, 2)
vNewRisk = vNewRisk.Offset(0, 1)
vNewRisk.Value = vTitle
Stop ' this is here for testing purposes

Everything is working except for step 4). I am getting a run-time error
'424':Object Required.

I'm not much of a programmer (still learning this VB stuff) so would
appreciate some help in getting this working.

TIA

Dave

What is wrong with this?
 

instead of:
vNewRisk = vNewRisk.Offset(0, 1)
try:
Set vNewRisk = vNewRisk.Offset(0, 1)

--
Gary''s Student - gsnu200815


"Risky Dave" wrote:

Hi,

I have a piece of code that isn't working the way I need it to. The
intention is to:
1) unprotect a sheet (working)
2) find the first blank line in column A (working)
3) copy a unique number from another sheet in this cell (working)
4) copy data from a third sheet to the cell immediately to the right of the
one that has just been copied to (not working)

The code I have for this is:

Dim vNewRisk As Variant ' used to find blank cell
Dim rLookUpRange As Range ' range to Vlookup on the
Identification sheet
Dim sRiskNumber As String ' unique identifier of new risk
Dim vTitle As Variant ' used to copy title data from
Identification page

Sheets("Treatment - Controls").Unprotect
Set vNewRisk = Sheets("Treatment - Controls").Range("a8")
Do Until vNewRisk.Value = "" ' look for first
blank cell
Set vNewRisk = vNewRisk.Offset(1, 0)
Loop

sRiskNumber = Sheets("user data").Range("b7")
Set rLookUpRange = Sheets("identification").Range("a:b")
vNewRisk.Value = sRiskNumber ' put new risk
number into first blank line
vTitle = Application.VLookup(sRiskNumber, rLookUpRange, 2)
vNewRisk = vNewRisk.Offset(0, 1)
vNewRisk.Value = vTitle
Stop ' this is here for testing purposes

Everything is working except for step 4). I am getting a run-time error
'424':Object Required.

I'm not much of a programmer (still learning this VB stuff) so would
appreciate some help in getting this working.

TIA

Dave

What is wrong with this?
 

It's always the simple things, isn't it: :-)

Many thanks

"Gary''s Student" wrote:

instead of:
vNewRisk = vNewRisk.Offset(0, 1)
try:
Set vNewRisk = vNewRisk.Offset(0, 1)

--
Gary''s Student - gsnu200815


"Risky Dave" wrote:

Hi,

I have a piece of code that isn't working the way I need it to. The
intention is to:
1) unprotect a sheet (working)
2) find the first blank line in column A (working)
3) copy a unique number from another sheet in this cell (working)
4) copy data from a third sheet to the cell immediately to the right of the
one that has just been copied to (not working)

The code I have for this is:

Dim vNewRisk As Variant ' used to find blank cell
Dim rLookUpRange As Range ' range to Vlookup on the
Identification sheet
Dim sRiskNumber As String ' unique identifier of new risk
Dim vTitle As Variant ' used to copy title data from
Identification page

Sheets("Treatment - Controls").Unprotect
Set vNewRisk = Sheets("Treatment - Controls").Range("a8")
Do Until vNewRisk.Value = "" ' look for first
blank cell
Set vNewRisk = vNewRisk.Offset(1, 0)
Loop

sRiskNumber = Sheets("user data").Range("b7")
Set rLookUpRange = Sheets("identification").Range("a:b")
vNewRisk.Value = sRiskNumber ' put new risk
number into first blank line
vTitle = Application.VLookup(sRiskNumber, rLookUpRange, 2)
vNewRisk = vNewRisk.Offset(0, 1)
vNewRisk.Value = vTitle
Stop ' this is here for testing purposes

Everything is working except for step 4). I am getting a run-time error
'424':Object Required.

I'm not much of a programmer (still learning this VB stuff) so would
appreciate some help in getting this working.

TIA

Dave

What is wrong with this?
 

I make exactly the same kind of error at least 6 times a week!
--
Gary''s Student - gsnu200815


"Risky Dave" wrote:

It's always the simple things, isn't it: :-)

Many thanks

"Gary''s Student" wrote:

instead of:
vNewRisk = vNewRisk.Offset(0, 1)
try:
Set vNewRisk = vNewRisk.Offset(0, 1)

--
Gary''s Student - gsnu200815


"Risky Dave" wrote:

Hi,

I have a piece of code that isn't working the way I need it to. The
intention is to:
1) unprotect a sheet (working)
2) find the first blank line in column A (working)
3) copy a unique number from another sheet in this cell (working)
4) copy data from a third sheet to the cell immediately to the right of the
one that has just been copied to (not working)

The code I have for this is:

Dim vNewRisk As Variant ' used to find blank cell
Dim rLookUpRange As Range ' range to Vlookup on the
Identification sheet
Dim sRiskNumber As String ' unique identifier of new risk
Dim vTitle As Variant ' used to copy title data from
Identification page

Sheets("Treatment - Controls").Unprotect
Set vNewRisk = Sheets("Treatment - Controls").Range("a8")
Do Until vNewRisk.Value = "" ' look for first
blank cell
Set vNewRisk = vNewRisk.Offset(1, 0)
Loop

sRiskNumber = Sheets("user data").Range("b7")
Set rLookUpRange = Sheets("identification").Range("a:b")
vNewRisk.Value = sRiskNumber ' put new risk
number into first blank line
vTitle = Application.VLookup(sRiskNumber, rLookUpRange, 2)
vNewRisk = vNewRisk.Offset(0, 1)
vNewRisk.Value = vTitle
Stop ' this is here for testing purposes

Everything is working except for step 4). I am getting a run-time error
'424':Object Required.

I'm not much of a programmer (still learning this VB stuff) so would
appreciate some help in getting this working.

TIA

Dave