Populating cells

[Kevin Stecyk]

Hi,

I have this seemingly easy problem that has me stumped.

I want to populate cells, starting on Sheet3!A1. I want to use 1000 rows
and as many columns as necessary (it will be less than 10). I don't know in
advance how many cells will be required.

Below is a snippet of my code. Watch for the line wrap.

My row number appears to work, though it seems to me to be a bit clumsy.
Any better suggestions most appreciated. The column number does not work
correctly. For example, if the iCounter1=1000, it should be on A1000.
Instead, my code will give B1000 (as it should given what I have coded).
How do I fix this such that I use the first 1000 rows, and as many columns
as necessary?

Thank you.

Regards,
Kevin


For Each rnCell In rnRange1
Set rnMatchData = rnRange2.Find(rnCell.Value, LookIn:=xlValues)
If rnMatchData Is Nothing Then
If iCounter1 Mod 1000 = 0 Then
iRowNum = 1000
Else
iRowNum = iCounter1 Mod 1000
End If
iColNum = Int(iCounter1 / 1000) + 1
Worksheets("Sheet3").Cells(iRowNum, iColNum).Value =
rnCell.Value
iCounter1 = iCounter1 + 1
End If
Next rnCell

 

[Tom Ogilvy]

icounter = 1000
? Int(iCounter1 / 1000) + 1
1

Looks like it would be column A to me?

 

[Kevin Stecyk]

Hi Tom,

iCounter1=1000

Int(1000/1000)+1 = 1 + 1 = 2

So I get column 2 or B.

I thought the same as you until I single stepped through it. I then I saw
A999 and the next cell was B1000.

Regards,
Kevin

 

[Kevin Stecyk]

Hi Tom,

I just recalled a solution by J.E. McGimpsey for his roundup function. It
is helpful here. It solves my iCounter1=1000 problem.

Here is my solution now.

Regards,
Kevin

For Each rnCell In rnRange1
Set rnMatchData = rnRange2.Find(rnCell.Value, LookIn:=xlValues)
If rnMatchData Is Nothing Then
If iCounter1 Mod 1000 = 0 Then
lRowNum = 1000
Else
lRowNum = iCounter1 Mod 1000
End If
sColNumTempVar = iCounter1 / 1000
lColNum = Fix(sColNumTempVar - Sgn(sColNumTempVar) *
(sColNumTempVar <> Fix(sColNumTempVar)))
Worksheets("Sheet3").Cells(lRowNum, lColNum).Value =
rnCell.Value
iCounter1 = iCounter1 + 1
End If
Next rnCell