Problem with Cells Syntax

Resolved · Medium Priority · Version 2016

Mon 17 Jun 2019, 14:39 Delegate Diane said...

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

Mon 17 Jun 2019, 21:40 Trainer Jens said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Using an equal (=) sign that isn't part of a formula

Before you type the equal sign, type an apostrophe: '
Then type your equal sign: = (and anything else you want to add after your equal sign)
Press ENTER.

(the apostraphe will disappear

View all Excel hints and tips