using variables to reference a cell

ToffaJ

New Member
Joined
Sep 15, 2002
Messages
27
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???
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Colo

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
Welcome! There are some ways.

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

ToffaJ

New Member
Joined
Sep 15, 2002
Messages
27
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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

New Member
Joined
Sep 15, 2002
Messages
27
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

MrExcel MVP,
Joined
Mar 20, 2002
Messages
1,456
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>
 

Forum statistics

Threads
1,144,050
Messages
5,722,234
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top