Mark or Gary I need some guidance with a VBA error 1004

[xraymitch]

I posted this request for help a week ago and so far no one apart from me has viewed or commented but perhaps I didn't make myself clear.

I am Logging Current Prices into multiple sheets and Auto Selecting markets 15 minutes before the off. In addition, with my basic VBA skills, I am trying to record the distance and race duration times since the off.

As long as only one sheet is active everything works perfectly and the Range("W1:AH1") gets recorded to the sheet.

If for example, 2 or more markets are active as in say the 15:00 and 15:15, then at some point I get the error 1004 which freezes all markets from loading etc.

I have spent many hours trying to understand Methods, Properties Objects etc,and I am dismayed by my inability to grasp how to implement the code properly, I woud be obliged if you Mark, or Gary could at least give me a clue or point me in the right direction.

Here below is the link to my post of last Thusday:

viewtopic.php?f=5&t=10281#p52292

Many thanks,

xraymitch

[Captain Sensible]

Only had a quick look but is there any major reason why you're using .Select and .Activate to do simple copies? You can simply copy one range to another by using something like

Code: Select all

Range("W1:AH1").Value = Range("A1:L1").Value

It could be that as you're code is firing off the active focus is bouncing between sheets. It's generally considered good practice to fully reference your cells because if a sheet other than the one you're working on becomes active any changes would happen on the new sheet and previous objects/references may no longer be valid.

i.e

Code: Select all

Sheets("Sheet1").Range("C21").Value

or use

With Target.Parent


etc

[Captain Sensible]

There's also a Workbook.SheetChange Event you might want to consider using in ThisWorbook to save you duplicating the same code for each sheet.

[xraymitch]

Captain Sensible as always I just want to thank you

"Only had a quick look but is there any major reason why you're using .Select and .Activate to do simple copies? You can simply copy one range to another by using something like"

This is something I have discovered and wanted to use.

"It could be that as you're code is firing off the active focus is bouncing between sheets. It's generally considered good practice to fully reference your cells because if a sheet other than the one you're working on becomes active any changes would happen on the new sheet and previous objects/references may no longer be valid."

This is my thinking to.


I wanted to try finding a way of creating Range("AT43:BE43").Value = Range("W1:AH1").Value and tried experimenting with the following:

Dim RaceM5 As Range
Set RaceM5 = Range("W1:AH1").Value

Dim RecordM5 As Range
Set RecordM5 As Offset(Cells(1, 27), 23) this code would produce an error immediately in VBA

I tried experimenting with Ranges and my thinking here was that if at some point cell W1 was made the Active cell then the value contained in cell AA1 would give the offset row number and 23 would give the offset column number for the next record. Thus if AA1 contained 52, then RecordM5 would equate to the Range("AT53").

Because of the error in the above code I never progressed into working out how I could add the "BE43" part to the range but ultimately I wanted to wanted to be able to write RecordM5.Value = RaceM5.Value

I also tried writing as Worksheets("Sheet5").Range("AT43:BE43").Value = Worksheets("Sheet5").Range("W1:AH1").Value

To put in in simple terms all I want to do is copy the Range("W1:AH1") which contains the data for every race.

[Captain Sensible]

To put in in simple terms all I want to do is copy the Range("W1:AH1") which contains the data for every race.

I'm still a bit confused as to why you're finding it a problem to copy Worksheets("Sheet5").Range("W1:AH1").Value into Worksheets("Sheet5").Range("AT43:BE43").Value with

Code: Select all

Worksheets("Sheet5").Range("AT43:BE43").Value =Worksheets("Sheet5").Range("W1:AH1").Value

If you wanted to retain the previous data when new races are added you could simply use something like below so you push the data in AT43:BE43 down 1 cell before adding new data, if you wanted to add new data into AT44:BE44 instead that's easy to do also using something like .End(xlUp).Row to find the last cell row with data

Code: Select all

With Target.Parent

.Range("AT43:BE43").Insert (xlShiftDown)
.Range("AT43:BE43").Value =.Range("W1:AH1").Value

End With

[xraymitch]

I'm still a bit confused as to why you're finding it a problem to copy Worksheets("Sheet5").Range("W1:AH1").Value into Worksheets("Sheet5").Range("AT43:BE43").Value with

Code: Select all

Worksheets("Sheet5").Range("AT43:BE43").Value =Worksheets("Sheet5").Range("W1:AH1").Value

To be honest I have forgotten why it was a problem - I had tried so many attempts that I got confused and reached burn out. I suspect the above code did work but I couldn't see how to increment the range which is why I was trying to work with the offset command.

Based on the examples you have shown me below and the new understanding you have given me I will start afresh and try to implement accordingly.

If you wanted to retain the previous data when new races are added you could simply use something like below so you push the data in AT43:BE43 down 1 cell before adding new data, if you wanted to add new data into AT44:BE44 instead that's easy to do also using something like .End(xlUp).Row to find the last cell row with data

Code: Select all

With Target.Parent

.Range("AT43:BE43").Insert (xlShiftDown)
.Range("AT43:BE43").Value =.Range("W1:AH1").Value

End With



There's also a Workbook.SheetChange Event you might want to consider using in ThisWorbook to save you duplicating the same code for each sheet.

A big THANKYOU Captain for taking the time to impart your knowledge and as I have said before in previous years - you are a STAR !!