variable doesn't reference correct column

[Andyjim]

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

 

[Don Guillett]

Perhaps due to not having a dot in front of the range in your with. Woudn't
matter when doing from the active sheet but would if on a different sheet.
It appears that you could use the same macro with a variable for the column

I did NOT test this

Sub MoveCompletedTradesLoop()
myoffset=0
'or an input box asking for the offset
'myoffset=inputbox("Enter column offset such as 0 or 1")

'Define Variables
Dim TradesEntered As Range, Check As Range,
Set Check = Worksheets("Analysis").Columns(45) .offset(,myoffset)

'Define ItmSeq Range
set tradesentered =Sheets("Analysis").Range("at17:at56")
For Each Check In TradesEntered
If Check = "True" Then
Check.EntireRow.Copy
Sheets("TradeHistory").Range("A4").End(xlDown). _
Offset(1, myoffset).PasteSpecial Paste:=xlPasteValues
end if
Next
End Sub

 

[Rick Rothstein \(MVP - VB\)]

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]

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!

 

[Andyjim]

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

 

[Don Guillett]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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