using variables to reference a cell

[ToffaJ]

Hi guys...
I've got a macro where I want to use a counter variable, then use that variable to reference a cell.
eg... if I've got a variable called intCount, I want to reference Column Q, row intCount... I've tried Range(Q(intCount)) but it doesn't work... any thoughts???

 

[Colo]

Welcome! There are some ways.

1. cells(intCount,"Q").select
2. range("Q" & intCount).select
3. [Q1].offset(intCount-1).select

 

[ToffaJ]

It doesn't want to work.. I've declared the variables as globals as it want's to. the line i've got is

If (Range("Q" & intCount).Select = "NO") Then

It gives me "Method 'Range' of object '_Global' failed.

it must be my declarations.. how else can you define them???

 

[Colo]

If (Range("Q" & intCount).Select = "NO") Then
It gives me "Method 'Range' of object '_Global' failed.

Hi ToffaJ, me again.
Yes, it is not corrct Syntax. Please let me know what do you would like to?

 

[ToffaJ]

Sub SLA()
intCounter = 1
intRowCount = 2
Sheets("Helpdesk").Select
Do While (intCounter < 2000)
If (Range("Q" & intCounter) = "NO") Then
Range("A" & intCounter).Select
Selection.Copy
Sheets("SLA").Select
Application.Goto Reference:=Range("A" & intRowCount)
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
intRowCount = intRowCount + 1
End If
intCounter = intCounter + 1
Loop
End Sub

Basically, Copy cell AintCounter where QintCounter has "NO" in the cell. It seems easy enough i would of thought, but it copies the first one, then skips over all other "NO"'s in that column...

 

[Colo]

The reason it does not work is, the code line 'Range("A" & intCounter).Select' should be after
'Do While (intCounter < 2000)'.
<PRE><FONT color=red>Sub </FONT>SLA()

<FONT color=red>Dim </FONT>intCounter <FONT color=red>As</FONT><FONT color=red> Integer</FONT>

<FONT color=red>Dim </FONT>intRowCount <FONT color=red>As</FONT><FONT color=red> Integer</FONT>

intCounter = 1

intRowCount = 2

<FONT color=red>Do </FONT>While (intCounter < 2000)

Sheets("Helpdesk").Select

<FONT color=red>If </FONT>Range("Q" & intCounter).Value = "NO" Then

Range("A" & intCounter).Select

Selection.Copy

Sheets("SLA").Select

Application.Goto Reference:=Range("A" & intRowCount)

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _

SkipBlanks:=False, Transpose:=False

intRowCount = intRowCount + 1

<FONT color=red>End If</FONT>

intCounter = intCounter + 1

<FONT color=red>Loop</FONT>

<FONT color=red>End Sub</FONT>
</PRE>

Here is fast way by using AutoFilter method. Q1 is title yes?

<PRE><FONT color=red>Sub </FONT>SLA_Colo_Modified()

<FONT color=red>With </FONT>Sheets("Helpdesk")

.[Q1:Q2000].AutoFilter 1, "NO"

Intersect(.Range(.[A2], .[A65536].End(xlUp)), .[A2:A2000]).Copy

Sheets("SLA").[A2].PasteSpecial xlValues

.[Q1:Q2000].AutoFilter

Application.CutCopyMode =<FONT color=red> False</FONT>

<FONT color=red>End With</FONT>

<FONT color=red>End Sub</FONT>
</PRE>