Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"Disk is Full" add-on question to "Can't reset last cell" post tod | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |