Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Range and cells syntax
Hi
I am trying to assign values from an array, using the cells command to identify the row and column. I am getting a run time error Method 'Range' of object '_global' failed. Here is an excerpt of the syntax. What am I doing wrong please?
NumberOfRows = UBound(MonitorsAndPcs, 1)
So basically I am checking if column 5 in each row of the array is = to Swanley. Dependant on the result, the array row is being written to one of two sheets. To write the row I am using the range/cells method which is causing my error.
Kind regards
Diane
For iRows = 1 To NumberOfRows
If MonitorsAndPcs(iRows, 5) = "Swanley" Then 'emulate monitor
Sheets(3).Activate
iSheetCol = 1
For iArrayCol = 1 To 7
Range(Cells(iMonitorRow, iSheetCol)) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
Next iArrayCol
iMonitorRow = iMonitorRow + 1
Else
Sheets(5).Activate
iSheetCol = 1
For iArrayCol = 1 To 7
Range(Cells(iPcRow, iSheetCol)) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
Next iArrayCol
iPcRow = iPcRow + 1
End If
Next iRows
RE: Range and cells syntax
Hi Diane,
Thank you for the forum question.
You are busy with your VBA. I am happy that you use the knowledge you got from our VBA courses.
Change the two lines below:
Range(Cells(iMonitorRow, iSheetCol)) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
Range(Cells(iPcRow, iSheetCol)) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
to:
Cells(iMonitorRow, iSheetCol) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
Cells(iPcRow, iSheetCol) = "monitor " & MonitorsAndPcs(iRows, iArrayCol)
Let me know if you still get an error message.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector