Posted to microsoft.public.excel.programming
|
|
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
|