Diane has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Problem with Cells Syntax
I have written some code which executes within a loop (the loop is fine), I wanted to refine the code to use the cells object which I understand to be the better method of coding but I get a runtime error.
This is the code which will not work
Range(Cells(iCopy, 2)).Offset(0, 2).Copy
'Target.Range(Cells(iCopy, 1)).PasteSpecial xlPasteValues
This is the code that does work
Range("b" & iCopy).Offset(0, 2).Copy
Target.Range("a" & iCopy).PasteSpecial xlPasteValues 'project name
CAn you please advise me of the correct syntax. Thanks
RE: Problem with Cells Syntax
Hi Diane,
Thank you for the forum question.
Replace:
Range(Cells(iCopy, 2)).Offset(0, 2).Copy
With:
Cells(iCopy,4).copy
The cells object is not valid inside the range object and I cannot see you need the Offset.
The target object must come from another part of the code, you haven't put in the question.
Without all the code I will not be able to find out if
Target.Cells(icopy, 1).PasteSpecial xlPasteValues
cna replace:
'Target.Range(Cells(iCopy, 1)).PasteSpecial xlPasteValues
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