32CARDS
Board Regular
- Joined
- Jan 1, 2005
- Messages
- 123
Currently I am using a Copy and Paste to "fix" this time consuming problem within a VBA Loop.
The current VBA is in Sheet Name: "Timer" is:
The method requires to Find a "marker" which is "1^", then move 1 cell to the right for the Value next to the marker. The marker and the Value positioning is is random, it's never in the same cell range.
In Sheet2 Range A1, this is a fixed cell range.
I have tried various ways to not have do a Copy, this is where the macro make take up to 2 seconds to do, even when using the F8 key. It seems to eat up a bit of memory to just for this minuscule task.
When I try a reference without the Selection.Copy, the Value does not appear when using Sheets("Sheet2").Range("A1").Value = ActiveCell.Value
How can I make the Value of the cell to the right of the Find's marker
appear in Sheets("Sheet2").Range("A1")
without the Copy ?
This "kwik fix" method has to do 3 references, @ ~ 2 seconds wasted time each, is around 6 seconds in total of wasted time, and what ever amount of CPU/Memory resources it wastes also.
I was hoping for some sort of string_reference method so Range A1's Value from Active Cell value is referenced by "string" ?
Thanks
The current VBA is in Sheet Name: "Timer" is:
Code:
Sub kwik_fix ()
Sheets("Timer").Select
Application.DisplayAlerts = False
Cells.Find(What:="1^", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
SearchFormat:=False).Activate
Selection.Offset(0, 1).Select '*Right*
Selection.Copy
Sheets("Sheet2").Range("A1").Value = ActiveCell.Value
Sheets("Sheet2").Select
Sheets("Sheet2").Range("A1").Select
End Sub
The method requires to Find a "marker" which is "1^", then move 1 cell to the right for the Value next to the marker. The marker and the Value positioning is is random, it's never in the same cell range.
In Sheet2 Range A1, this is a fixed cell range.
I have tried various ways to not have do a Copy, this is where the macro make take up to 2 seconds to do, even when using the F8 key. It seems to eat up a bit of memory to just for this minuscule task.
When I try a reference without the Selection.Copy, the Value does not appear when using Sheets("Sheet2").Range("A1").Value = ActiveCell.Value
How can I make the Value of the cell to the right of the Find's marker
appear in Sheets("Sheet2").Range("A1")
without the Copy ?
This "kwik fix" method has to do 3 references, @ ~ 2 seconds wasted time each, is around 6 seconds in total of wasted time, and what ever amount of CPU/Memory resources it wastes also.
I was hoping for some sort of string_reference method so Range A1's Value from Active Cell value is referenced by "string" ?
Thanks