Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating cells
icounter = 1000
? Int(iCounter1 / 1000) + 1 1 Looks like it would be column A to me? -- Regards, Tom Ogilvy "Kevin Stecyk" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating cells
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 "Tom Ogilvy" wrote in message ... icounter = 1000 ? Int(iCounter1 / 1000) + 1 1 Looks like it would be column A to me? -- Regards, Tom Ogilvy "Kevin Stecyk" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating cells
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 "Kevin Stecyk" wrote in message ... 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 "Tom Ogilvy" wrote in message ... icounter = 1000 ? Int(iCounter1 / 1000) + 1 1 Looks like it would be column A to me? -- Regards, Tom Ogilvy "Kevin Stecyk" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating Cells | Excel Discussion (Misc queries) | |||
Randomly populating empty cells with other text cells | Excel Discussion (Misc queries) | |||
Help with auto populating cells. | Excel Worksheet Functions | |||
Populating empty cells | Excel Worksheet Functions | |||
Populating several cells | Excel Worksheet Functions |