0

I am copying and pasting data into blocks of cells which move down the spreadsheet in regular blocks for each brand - I am therefore trying to create a variable linked to Brand Number, but am receiving this error.

Hopefully a simple one, hoping somebody can help!

Dim StartCell1 As String
Dim BrandNumber As Integer
Dim DblQuote As String

StartCell1 = vbDblQuote + "B" + CStr((3 + (27 * (BrandNumber - 1)))) + vbDblQuote

BrandNumber = 1

Range(StartCell1).Select

Run-time error '1004':
Method 'Range' of object '_Global' failed

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Thomas Gardner
  • 53
  • 5
  • check the value of `StartCell1`, if you add a line `Debug.Print StartCell1` before the line of `Range(StartCell1).Select` what do you get in the immediate window ? Also what is the value of `BrandNumber`, is it larger then 2 ? – Shai Rado Mar 06 '19 at 11:27
  • You wont need the double quotes. I think `B` and your number should be good in a string. So your string will be `b3` not `"b3"` – Nathan_Sav Mar 06 '19 at 11:32
  • Hi - Tried without the quotes didn't work, exactly the same issue and error! Thanks though Shai Rado - Brand Number Starts at 1 (but multiplying 0 by 27 and adding 3 should be fine, right?) And apologies, being new-ish to VBA error handling. I've tried the Debug.Print, I get the same error as it seems that it's simply jumping straight over the Debug.Print. Also I'm used to Python...where is the output console where I would see the Value?! Thanks! – Thomas Gardner Mar 06 '19 at 11:35
  • You should avoid using select. You can try with Range objects `Dim CopyRange As Range` and then `Set CopyRange = Range("A1:A100")` for example, so you could just `CopyRange.Copy Destination:=Range("A101")` – Damian Mar 06 '19 at 11:37
  • By the way, your `BrandNumber = 1` should go before the `StartCell1 = `... Otherwise BrandNumber will be -1 and that will give you an error. – Damian Mar 06 '19 at 11:40
  • @ThomasGardner the output window is called Locals. WHat is `vbDblQuote` – Nathan_Sav Mar 06 '19 at 11:43
  • Your brandnumber not being set, will result in a -ve result, which translates to `""B-24""` using your code. You need to get rid of the double quotes. – Nathan_Sav Mar 06 '19 at 11:45

1 Answers1

0
Dim StartCell  As Range
Dim BrandNumber As Integer
BrandNumber = 1
set StartCell = Range("B" & (3 + (27 * (BrandNumber - 1))))

Excel will convert the number returned by (3 + (... into a string automatically. I've created an object variable to point to startcell so you can just copy it to a destination in one line - this would copy it to A2 for example

StartCell.Copy Range("a2")

Never use Select How To Avoid using Select in VBA

Harassed Dad
  • 4,669
  • 1
  • 10
  • 12