Fixed Cell Range Value to Equal random Active Cell range Value

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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Works fine on my spreadsheet with a value = value. Avoid all that selecting though.

Rich (BB code):
Sub kwik_fix()
    Dim x As Range

    Application.DisplayAlerts = False

    On Error Resume Next
    Set x = Sheets("Timer").Cells.Find(What:="1^", After:=Sheets("Timer").Cells(1, 1), LookIn:=xlValues, LookAt:= _
                                       xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, _
                                       SearchFormat:=False)

    On Error GoTo 0
    Sheets("Sheet2").Range("A1").Value = x.Offset(0, 1).Value

    With Application
        .Goto Sheets("Sheet2").Range("A1")
        .DisplayAlerts = True
    End With

End Sub
 
Last edited:
Upvote 0
I have just read this part
The current VBA is in Sheet Name: "Timer"
.
Why is it in the sheet module and not a regular module and how are you calling the macro?
 
Upvote 0
MARK858
Why is it in the sheet module and not a regular module and how are you calling the macro?

My apologies, wording was not correct and good it is pointed out.
The VBA is in a Module, not within the sheet's module.


MARK858
In regards to your solution,
I have to implement the code then do a test run. As you say it works, I thought it would work to, I have similar Active cell = cell range everywhere else, but not in this case, and only that section, everywhere else it works fine.

I'll check the format and post back after the tests

Thanks
 
Upvote 0
Re: Fixed Cell Range Value to Equal random Active Cell range Value-SOLVED

For the record, the format is General and it's all numerical.

It worked !
SOLVED

Thanks
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top