New issue with "With" statement

R

robs3131

Hi all,

I'm trying to write code to have a set of variables capture the values of
specific cells in one sheet ("Member ID Report Master") and then later input
the values captured to a specific cell location in another sheet ("Open
Transactions"). I'm getting the following error with the code below --
specifically, the line of code with the ">>" is highlighted as having the
error -- any suggestions on how to address this? Thanks in advance.

Error message:
"Run-time error '438': Object doesn't support this property or method"


Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim membername

With Sheets("Unique Member IDs")
If Len(.Range("A3")) <> 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) <> 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then
With Sheets("Member ID Report Master")Set merchantid = .Range(.F.Address()).Offset(0, -2).Value
Set membername = .Range(.F.Address()).Offset(0, -1).Value
End With
With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = membername
'.Range("D1").Offset(x, 1).Value = Worksheets("Member ID
Report Master").Range(F.Address()).Offset(0, 6).Value
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"

x = x + 1
End With

Else
End If
Next

End Sub
 
J

Jim Cone

Robert,
From not enough dots to too many <g>...

"Set memberid = .Range(.F.Address()).Offset(0, -3).Value"

could be a range...
Set memberid = F.Offset(0, -3)

or could be a value...
memberid = F.Offset(0, -3).value
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"robs3131"
<[email protected]>
wrote in message
Hi all,

I'm trying to write code to have a set of variables capture the values of
specific cells in one sheet ("Member ID Report Master") and then later input
the values captured to a specific cell location in another sheet ("Open
Transactions"). I'm getting the following error with the code below --
specifically, the line of code with the ">>" is highlighted as having the
error -- any suggestions on how to address this? Thanks in advance.

Error message:
"Run-time error '438': Object doesn't support this property or method"


Code:
Sub opentransids()

Dim uniqueidsopen As Range
Dim F 'individual record within "uniqueidsopen" range
Dim payclosed As Range
Dim G 'individual record within "payclosed" range
Dim x 'variable to determine how many rows down to input uniqueidsopen
Dim c 'variable to determine whether or not a uniqueidsopen is also within
payclosed
Dim memberid
Dim merchantid
Dim membername

With Sheets("Unique Member IDs")
If Len(.Range("A3")) <> 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))
Else
Set uniqueidsopen = .Range("A2")
End If
End With

With Sheets("Payment Sales Master")
If Len(.Range("H3")) <> 0 Then
Set payclosed = .Range("H2", .Range("H2").End(xlDown))
Else
Set payclosed = .Range("H2")
End If
End With

x = 1
For Each F In uniqueidsopen
c = 0
For Each G In payclosed
If F = G Then
c = c + 1
Else
End If
Next

If c = 0 Then
With Sheets("Member ID Report Master")Set merchantid = .Range(.F.Address()).Offset(0, -2).Value
Set membername = .Range(.F.Address()).Offset(0, -1).Value
End With
With Sheets("Open Transactions")
.Range("D1").Offset(x, 0).Value = F
.Range("D1").Offset(x, -3).Value = memberid
.Range("D1").Offset(x, -2).Value = merchantid
.Range("D1").Offset(x, -1).Value = membername
'.Range("D1").Offset(x, 1).Value = Worksheets("Member ID
Report Master").Range(F.Address()).Offset(0, 6).Value
.Range("D1").Offset(x, 3).Value = "Payment not yet submitted
for this Sales transaction"

x = x + 1
End With

Else
End If
Next

End Sub
 
B

Bob Phillips

To explain that, F is an object variable in its own right, it is not a
member of the Sheets("Member ID Report Master") object, so no need to dot
qualify it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

robs3131

Hi guys,

The thing I'm not getting though is that I want to set the variables to
values that are within the "Member ID Report Master" sheet -- specifically, I
want the code to locate F within this sheet and then set the variables to a
value that is in a cell relative to the cell that the F value is in (ie -
offset (0, -3), etc.). If I don't qualify using "With Sheets("Member ID
Report Master")", then isn't it possible that the code will use the active
sheet, which may be different than the "Member ID Report Master"?

Thanks,
 
B

Bob Phillips

F will refer to the sheet that is active or specifically specified when it
is loaded. Your code says

With Sheets("Unique Member IDs")
If Len(.Range("A3")) <> 0 Then
Set uniqueidsopen = .Range("A2", .Range("A2").End(xlDown))

and then later

For Each F In uniqueidsopen

so uniqueids refers specifically to Sheets("Unique Member IDs"), and by
implication, F also does.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
R

robs3131

Thanks Bob! If that is how the logic works, then that really helps a lot.
I'll post if I have any other questions regarding this.
 
R

robs3131

Hi Bob,

I tried this both ways you listed below and I'm getting "Run-Time error
'1004'" both ways...any thoughts?
 
J

Jim Cone

I assume Bob is probably with a paying customer, so...

If you are in Column A there is no room to offset 3 columns to the left...
To use F.Offset(0, -3) you must be in column D or higher.

Also, .Range(F.Address) is a perfectly acceptable use
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware





"robs3131" <[email protected]>
wrote in message
Hi Bob,

I tried this both ways you listed below and I'm getting "Run-Time error
'1004'" both ways...any thoughts?
 
B

Bob Phillips

No paying customer, just sleeping, I'm in the UK.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Issue with Cells.Find statement within With 4
VBA Coding Help for Beginner 0
Issue with code execution -- it is very slow 8
For Eachcell in Range tranpose comma seperated values to a list in D 4
Sheet to sheet code Type Mismatch 6
Object variable or with Block variable not set 9
Code does not error but no copy either 4
Select Case code error 8

Top