Nth Row 2

[RicksAtWork]

I am using:

Journal = Application.Cells(nthRow, 21).Value

To pull the value from the nth Row column 21.

However, my data has been filtered, so it may read

Row
1
2
6
7

I want to iterate across the visible rows.

Obviously

for i=1 to 4

Journal = Application.Cells(i, 21).Value

wont work.....

How can I iterate over rows 1,2,6,7 and not 1,2,3,4???

 

[PHV]

Have a look at the SpecialCells method of the Range object (xlCellTypevisible)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Check out the SpecialCells Method

for each r in MyRange.SpecialCells(xlCellTypeVisible)

Next

BTW, the Application object is NOT the parent of Cells. It should be a Sheet OBject.


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[RicksAtWork]

SkipVought, you are a star.

A final thought...

I have the following:

Set JournalRange = ActiveSheet.AutoFilter.Range.Columns("X")

For Each r In JournalRange.SpecialCells(xlCellTypeVisible)

Journal = r.Value()

If (Journal <> "") Then

JournalImpactFactor = JournalImpactFactor + Application.WorksheetFunction.VLookup(Journal, Range("Scoring_JournalTitle"), 4, False)

End If


Next

I am iterating across the filtered Row and pulling a cell value with which I populate Journal

If Journal has a value I want to use a VLookUp to pull a value from a second sheet.

This is done with the following line:

JournalImpactFactor = JournalImpactFactor + Application.WorksheetFunction.VLookup(Journal, Range("Scoring_JournalTitle"), 4, False)


I get the following error:

'Unable to use the VLookUp function of the WorkSheetfunction Class'

What am I doing wrong?

 

[SkipVought]

Hi,

If I were doing it, I'd use MS Query via Data/Get External Data/New Database Query -- EXCEL FILES -- YOUR WORKBOOK -- SHEET WITH AUTOFILTER.......

to generate a resultset for your filter criteria on a separate sheet.

Then use that list to populate your listbox.

I do this all the time!


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}