New issue with "With" statement
 

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 memberid = .Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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"

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 memberid = .Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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)

"Jim Cone" wrote in message
...
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"

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 memberid = .Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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,

--
Robert


"Bob Phillips" wrote:

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)

"Jim Cone" wrote in message
...
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"

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 memberid = .Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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)

"robs3131" wrote in message
...
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,

--
Robert


"Bob Phillips" wrote:

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)

"Jim Cone" wrote in message
...
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"

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 memberid =
.Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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.
--
Robert


"Bob Phillips" wrote:

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)

"robs3131" wrote in message
...
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,

--
Robert


"Bob Phillips" wrote:

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)

"Jim Cone" wrote in message
...
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"

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 memberid =
.Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

Hi Bob,

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


"Bob Phillips" wrote:

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)

"Jim Cone" wrote in message
...
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"

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 memberid = .Range(.F.Address()).Offset(0, -3).Value
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


--
Robert

New issue with "With" statement
 

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"
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?
--
Robert

New issue with "With" statement
 

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)

"Jim Cone" wrote in message
...

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"
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?
--
Robert

New issue with "With" statement
 

Thanks Jim for pointing that out. Makes sense!
--
Robert


"Jim Cone" wrote:


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"
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?
--
Robert