Working with data in Excel

[mike101]

I am trying to take the 2999'th value for the B column and the C column and place them in D1 and E1... then go to 2999*2 for D2 and E2, etc. Below is the code I have.

Sub Macro1()

Dim Counter As Integer
Dim Y As Integer

Counter = 1
Y = Counter * 2999

If (Counter < 5) Then
Range(&quot;E1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R[$Y]C[-2]&quot;
Range(&quot;F1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R[$Y]C[-4]&quot;
Counter = Counter + 1
Else

End If

End Sub

I am getting a debug error on the ActiveCell.FormulaR1C1 = &quot;=R[$Y]C[-2]&quot; line, I know its with the $Y part, I've tried multiple things... how do I make the [] equal to what Y is? Also, how do I make the loop repeat until it's run out've data lines? Thanks!

 

[Yesuslave]

Sub Macro1()

Dim Counter As Integer
Dim Y As Integer

Counter = 1
Y = Counter * 2999

If (Counter < 5) Then
Range(&quot;E1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R[&quot; & Y & &quot;]C[-2]&quot;
Range(&quot;F1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R[&quot; & Y & &quot;]C[-4]&quot;
Counter = Counter + 1
Else

End If

 

[Yesuslave]

By making the loop run until its out of data lines, do you mean until it finds a cell where there is no data, or that it runs 5 times and that is all?

 

[SQLBI]

Hi,

I think this should work for you.

Dim intCount As Integer
Dim intA As Integer
Dim intB As Integer
Dim intY As Integer

intCount = 1
intA = 1
intB = 2

Do While intCount <5
intY = intCount * 2999

Cells(intA, intB + 2).Value = Cells(intY, intB).Value
Cells(intA, intB + 3).Value = Cells(intY, intB + 1).Value

intCount = intCount + 1
intA = intA + 1

Loop

Hope this helps.



Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[Yesuslave]

Instead of a do until loop, you can also use the For Next loop as well.

Like this:

Sub Macro1()

Dim Counter As Integer
Dim Y As Integer

Counter = 1
Y = Counter * 2999

For i = 1 to 5
Range(&quot;E1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R&quot; & Y & &quot;C[-2]&quot;
Range(&quot;F1&quot.Select
ActiveCell.FormulaR1C1 = &quot;=R&quot; & Y & &quot;C[-4]&quot;
Counter = Counter + 1
Next

End If

 

[Yesuslave]

Blah, messed that, up, but hopefully you get the idea.

 

[mike101]

Awesome, thanks. I would like to try to figure out how to have it continue to loop until it finds cells with no data. Right now I just have a place-holder to do it 5 times and then quit... but I'd like it to keep going till it detects a blank column. Any ideas? thanks again!

 

[mike101]

For Counter = 1 To 5
Y = Counter * 3000
Range(&quot;E&quot; & Counter).Select
ActiveCell.FormulaR1C1 = &quot;=R&quot; & Y & &quot;C[-2]&quot;
Range(&quot;F&quot; & Counter).Select
ActiveCell.FormulaR1C1 = &quot;=R&quot; & Y & &quot;C[-4]&quot;
Counter = Counter + 1
Next

When I run that above with the other parts of my script, it is displaying 1, 3, and 5. It is also putting them in the proper cells, yet it's leaving 2 and 4 blank for some reason. Anybody know why? Thanks.

 

[PHV]

Remove this line, as the For loop increments already Counter:
Counter = Counter + 1

Hope This Help
PH.

 

[degs]

I count the number of rows by using awhile loop to go down column A until it finds a blank.

I increment the counter to tell me how many iterations is needed.

I then execute a for loop the desired number of times

 

[Loomah]

Mike
Check out the FAQs as there are at least 2 (xlbo's & mine) that will help you find the last row or column containing data.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244