Revisiting "New issue with "With" statment" post from 6/7/07
 

Hi Bob and all,

I tried this both ways you listed below and I'm getting "Run-Time error
'1004'" both ways...any thoughts on why this is coming up?
--
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



--
Robert

Revisiting "New issue with "With" statment" post from 6/7/07
 

Take a look at this section of code:

With Sheets("Member ID Report Master")
Debug.Print F.Address
memberid = F.Offset(0, -3).Value
merchantid = F.Offset(0, -2).Value
membername = F.Offset(0, -1).Value

'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

F.address when I run it is A2. The error is occurring because you are
using the offset to try to go to a cell that is 3 columns to the left of A2
which doesn't exist.

HTH,
Barb Reinhardt

"robs3131" wrote:

Hi Bob and all,

I tried this both ways you listed below and I'm getting "Run-Time error
'1004'" both ways...any thoughts on why this is coming up?
--
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



--
Robert

Revisiting "New issue with "With" statment" post from 6/7/07
 

Thanks Bob - this answers my question!
--
Robert


"Barb Reinhardt" wrote:

Take a look at this section of code:

With Sheets("Member ID Report Master")
Debug.Print F.Address
memberid = F.Offset(0, -3).Value
merchantid = F.Offset(0, -2).Value
membername = F.Offset(0, -1).Value

'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

F.address when I run it is A2. The error is occurring because you are
using the offset to try to go to a cell that is 3 columns to the left of A2
which doesn't exist.

HTH,
Barb Reinhardt

"robs3131" wrote:

Hi Bob and all,

I tried this both ways you listed below and I'm getting "Run-Time error
'1004'" both ways...any thoughts on why this is coming up?
--
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



--
Robert