Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
I am stumped. The subroutine below runs fine, but when I run a similar sub
using a different variable which is set to a different column, it goes back to checking the column in the first sub. I will show the sub that works (variable is ClosCheck). The second sub is identical except for the variable is PastCheck. I will be most grateful if you can solve this Sub MoveCompletedTradesLoop() 'Define Variables Dim TradesEntered As Range, ClosCheck As Range, Set ClosCheck = Worksheets("Analysis").Columns(46) 'Define ItmSeq Range With Sheets("Analysis") Set TradesEntered = Range("at17:at56") End With 'Loop: Check for complete trades, copy to Trade History For Each ClosCheck In TradesEntered If ClosCheck = "True" Then 'Make this section a Sub and call it here ClosCheck.EntireRow.Select Selection.Copy Sheets("TradeHistory").Select Range("A4").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate ActiveCell.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select Sheets("Analysis").Select Else MsgBox ("That's all") 'Goes with Else. Comment out Exit Sub 'Goes with Else. Comment it out. End If Next 'Ends "For Each" Loop Set ClosCheck = Nothing End Sub SECOND SUB: Sub MovePastTradesLoop() 'Define Variables Dim TradesEntered As Range, PastCheck As Range ' Set PastCheck = Worksheets("Analysis").Columns(48) 'Define ItmSeq Range With Sheets("Analysis") Set TradesEntered = Range("at17:at56") End With 'Loop: Check for complete trades, copy to Trade History For Each PastCheck In TradesEntered If PastCheck = "True" Then 'Make this section a Sub and call it here PastCheck.EntireRow.Select Selection.Copy Sheets("TradeHistory").Select Range("A4").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate ActiveCell.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select Sheets("Analysis").Select Else ' MsgBox ("OK") 'Goes with Else. Comment out Exit Sub 'Goes with Else. Comment it out. End If Next 'Ends "For Each" Loop 'Protect End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
I'm not sure what you are expecting to happen, but after you set this range...
Set ClosCheck = Worksheets("Analysis").Columns(46) you wipe out that setting and reset ClosCheck to each cell in the TradesEntered range after you enter the For Each loop here... For Each ClosCheck In TradesEntered You do the very same thing for the PastCheck range variable and, since TradesEntered is set to the same range in both routines, and since each For Each loop looks like it executes the same code statements, your two variables are being set to the same cells within TradesEntered, and having the same things done to them, while the loop is executing. The For Each statement is **sort of** a short cut for this pseudo-code (meaning, it is not **real** VBA syntax; but, rather, representative of a process)... For X = 1 To TradesEntered.Count Set ClosCheck = TradesEntered(X) Do Stuff with ClosCheck which is really TradesEntered(X) Next where I am equating the march through an array-like object called TradesEntered one at a time. The key thing to note is that with each loop, the ClosCheck variable is reset to a new cell reference. That is what happens to it inside your For Each loops too, the range variables are being reset to a new cell reference on each loop. Rick "Andyjim" wrote in message ... I am stumped. The subroutine below runs fine, but when I run a similar sub using a different variable which is set to a different column, it goes back to checking the column in the first sub. I will show the sub that works (variable is ClosCheck). The second sub is identical except for the variable is PastCheck. I will be most grateful if you can solve this Sub MoveCompletedTradesLoop() 'Define Variables Dim TradesEntered As Range, ClosCheck As Range, Set ClosCheck = Worksheets("Analysis").Columns(46) 'Define ItmSeq Range With Sheets("Analysis") Set TradesEntered = Range("at17:at56") End With 'Loop: Check for complete trades, copy to Trade History For Each ClosCheck In TradesEntered If ClosCheck = "True" Then 'Make this section a Sub and call it here ClosCheck.EntireRow.Select Selection.Copy Sheets("TradeHistory").Select Range("A4").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate ActiveCell.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select Sheets("Analysis").Select Else MsgBox ("That's all") 'Goes with Else. Comment out Exit Sub 'Goes with Else. Comment it out. End If Next 'Ends "For Each" Loop Set ClosCheck = Nothing End Sub SECOND SUB: Sub MovePastTradesLoop() 'Define Variables Dim TradesEntered As Range, PastCheck As Range ' Set PastCheck = Worksheets("Analysis").Columns(48) 'Define ItmSeq Range With Sheets("Analysis") Set TradesEntered = Range("at17:at56") End With 'Loop: Check for complete trades, copy to Trade History For Each PastCheck In TradesEntered If PastCheck = "True" Then 'Make this section a Sub and call it here PastCheck.EntireRow.Select Selection.Copy Sheets("TradeHistory").Select Range("A4").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate ActiveCell.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select Sheets("Analysis").Select Else ' MsgBox ("OK") 'Goes with Else. Comment out Exit Sub 'Goes with Else. Comment it out. End If Next 'Ends "For Each" Loop 'Protect End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
Hi Don-
Thanks for your quick response. I find I didn't quite size up the problem correctly. The second macro does find the correct column.(Column 48). But it runs the Check.EntireRow.copy regardless whether the column 48 value is true or false. If Check = "True" Then Check.EntireRow.Copy Thanks much! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
Hi Rick-
Thanks so much. I know I don't understand variables well enough. I tried to apply your suggestion.. Could you tell me where I am going wrong? Sub MovePastTradesLoop() 'Define Variables Dim TradesEnteredPast As Range, PastCheck As Range With Sheets("Analysis") Set TradesEnteredPast = Range("at17:at56") End With 'Loop: Check for complete trades, copy to Trade History For X = 1 To TradesEnteredPast.Count Set PastCheck = TradesEnteredPast(X) If PastCheck.Value = "True" Then PastCheck.EntireRow.Select ERRORS OUT HERE Selection.Copy Sheets("TradeHistory").Select Range("A4").Activate Selection.End(xlDown).Select ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate ActiveCell.EntireRow.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Application.CutCopyMode = False Range("A1").Select Sheets("Analysis").Select Range("A1").Select Else MsgBox ("OK") 'Goes with Else. Comment out Exit Sub 'Goes with Else. Comment it out. End If Next 'Ends "For Each" Loop End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
If you like, send me your workbook along with a complete explanation of what
you want. It appears that Rick's assessment may be correct but that a flexible one macro solution would work. -- Don Guillett Microsoft MVP Excel SalesAid Software "Andyjim" wrote in message ... Hi Don- Thanks for your quick response. I find I didn't quite size up the problem correctly. The second macro does find the correct column.(Column 48). But it runs the Check.EntireRow.copy regardless whether the column 48 value is true or false. If Check = "True" Then Check.EntireRow.Copy Thanks much! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
...along with a complete explanation of what you want...
This is the key. My observation, along with your observation about the missing dot operator inside the With/End With block, pinpoints the problem areas; however, I was not sure what the OP actually wanted to do... his Set'ting of the variables in question at the beginning of his code leads me to believe he was trying to applying some kind of restriction on how the code would proceed, but couldn't figure out what that restriction should actually be. Rick |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
variable doesn't reference correct column
...along with a complete explanation of what you want...
This is the key. My observation, along with your observation about the missing dot operator inside the With/End With block, pinpoints the problem areas; however, I was not sure what the OP actually wanted to do... his Set'ting of the variables in question at the beginning of his code leads me to believe he was trying to applying some kind of restriction on how the code would proceed, but couldn't figure out what that restriction should actually be. I think the OP has solved his problem as he is thanking you Chip, you and I in a response to Chip in a new thread he started on this subject. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with variable column reference | Excel Worksheet Functions | |||
Cell reference with variable column | Excel Worksheet Functions | |||
Variable column reference in formula | Excel Discussion (Misc queries) | |||
Copy formula down a column does not use correct cell reference | Excel Discussion (Misc queries) | |||
how do I use variable column and rows using other cell reference . | Excel Worksheet Functions |