Range.Cells over Integer Dependent Range

[Dave_Holland89]

Hello All

Just trying to use an integer as my relative column reference in the below. It works fine with a single cell but I get a '400' error when using it against a range of more than one cell.

For example, the below works just fine as it puts a value in cell E5 on the specified sheet

 ThisWb.Sheets("Tests by Week").Range(Cells(5, i)).Value = 1

However when I want to put the same value in a range of cells, as below (going in to E5:E10)

 ThisWb.Sheets("Tests by Week").Range(Cells(5, i), Cells(10, i)).Value = 1

I'm sure it must be simple but I can't figure out why it's throwing an error! Is it because I'm assigning a single value to 5 cells rather than 1:1 or 5:5?

Thanks

Dave

 

[Joe4]

It doesn't seem to like that code if you are not already on the "Tests by Week" sheet.

 

[Norie]

Dave

Try this.

With ThisWb.Sheets("Tests by Week")
    .Range(.Cells(5, i), .Cells(10, i)).Value = 1
End With

By the way, I'm surprised this works.

 ThisWb.Sheets("Tests by Week").Range(Cells(5, i)).Value = 1

As far as I can see it would only work if the value in Cells(5,i) was a valid cell reference.

Are you sure it wasn't this you used?

 ThisWb.Sheets("Tests by Week").Cells(5, i).Value = 1

 

[JLGWhiz]

Error 400? Are you running the code from a UserForm? Norie seems to be on track about the syntax, but the error 400 really confuses me.

 

[Dave_Holland89]

Apologies, it was indeed the below, long day (or that's what I'm blaming).

ThisWb.Sheets("Tests by Week").Cells(5, i).Value = 1

Even with a watch on it, it's frustrating that the 400 keeps appearing as it's next to useless for diagnostics.

So from the suggestions I've been able to make all the relevant cells equal 1, which was my originally query to test it, many thanks for that. My next obstacle is making a range equal another range, I've tried the below but to no avail.

[With ThisWb.Sheets("Tests by Week")
    .Range(.Cells(5, i), .Cells(10, i)).Value = ROTAWb.Sheets("Master")
    .Range(.Cells(5, i), .Cells(10, i)).Value
End With/CODE]

If I try fixed ranges, i.e. E5:E10 for each, it works no problem. However as part of a loop, it needs to put the range in the next column along on each loop so a fixed range is no good. 

I must have another syntax issue with this, any kind soul want to wade in?

Thanks again, great advice as ever on here.

Dave

 

[Norie]

Dave

The problem with the original code was that the 2 instances of Cells were unqualified, in the code I posted I used a With statement to fix that.

Unfortunately you can't use the same method when dealing with more than one sheet, as in your latest example.

There are some other options though.

Here's one, which I think you might agree is rather unwieldy.

ThisWb.Sheets("Tests by Week").Range(ThisWb.Sheets("Tests by Week").Cells(5, i), ThisWb.Sheets("Tests by Week").Cells(10, i)).Value _
= ROTAWb.Sheets("Master").Range(ROTAWb.Sheets("Master").Cells(5, i), ROTAWb.Sheets("Master").Cells(10, i)).Value

Here's another.

ThisWb.Sheets("Tests by Week").Cells(5, i).Resize(6).Value = ROTAWb.Sheets("Master").Cells(5, i).Resize(6).Value

And another.

Dim rngDst As Range
Dim rngSrc As Range

    With ThisWb.Sheets("Tests by Week")
        Set rngDst = .Range(.Cells(5, I), .Cells(10, I))
    End With
    
    With ROTAWb.Sheets("Master")
        Set rngSrc = .Range(.Cells(5, I), .Cells(10, I))
    End With
    
    rngDst.Value = rngSrc.Value

 

[JLGWhiz]

I find that putting the workbooks and sheets into one variable helps to eliminate typing errors and omissions in long statements.

Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = ThisWb.Sheets("Tests by Week")
Set sh2 = ROTAWb.Sheets("Master")
 For i = 1 To 5
  sh1.Cells(5, i).Resize(6, 1) = sh2.Cells((5, i).Resize(6, 1).Value
 Next