CODEWITHSUNDEEP
vbaexcel
Ban Atman
Ban Atman

Reputation: 177

specifying a cell in a sheet in an Excel macro

I am writing an Excel macro and have a section like the following where I go through a column in a sheet called "the daf" and compare it to a cell in another sheet:

For i = 2 To 75
    If Range("the daf!C" + Trim(Str(i))) = Range("scratchpad!Q30") Then
        notes = notes + Range("scratchpad!P30")
        Exit For
    End If
Next

The problem is that it produces the result of "#Value!" and the problem appears to be the fact that I have a space in the sheet name because when I replace "the daf" with "scratchpad" it does not produce an error. If what I am saying is correct, then how can I refer to a cell in a sheet when the sheet's name contains a space?

Upvotes: 1

Views: 1378

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149277

Use

Sheets("the daf").Range("C" & i)

instead of

Range("the daf!C" + Trim(Str(i)))

Example:

If Sheets("the daf").Range("C" & i).Value = Sheets("scratchpad").Range("Q30").Value Then

EDIT

If you still want to use the method that you are using then try this

Range("'the" & " " & "daf'!C" & i)

Upvotes: 2

Related Questions