PDA

View Full Version : marco in Excel - loop probably



dmkdomino
09-19-2014, 01:49 AM
Hi guys,
I am a newbie in VBA and I am trying to write a macro that makes something like this (attachment)
I was trying to use "do... while" but it didnt work.

i58.tinypic.com/2qkma1j.png


I made some kinda graph because it would be difficult to describe

Thanx in advance

Aussiebear
09-19-2014, 02:07 PM
Welcome to the forum. To gain an understanding of what it is that you are chasing, we will need a more descriptive narration of the issue.

For example, you have from the second column circled the 2nd, 3rd, and 4th figures. To create a logical rule, we need to know why those figures have been chosen and the outcome.

Secondly, from the first column you have circled the 2nd, 3rd and 4th values, and again to create a logical rule, we need to know why it is just those values, and if there is a relationship to the adjoining figures in column 2.

Thirdly, can you also explain the chosen values in columns 3 and 4, and yet again why these figures are chosen but not the others.

Lastly, it may be more beneficial to those assisting you, if you were to attach a workbook, by clicking on Go Advanced, then scroll down to manage attachments and follow the prompts from there

westconn1
09-20-2014, 01:30 AM
i can figure what you want, but post a workbook with sample data for testing

this is untested and may contain code errors or typos

For rw = 1 To Cells(Rows.Count, 3).End(xlUp).Row
With Cells(rw, 3)
If .Value > 1000 Then
tmp = 0
tot = .Offset(, -1).Value
Do Until .Offset(tmp, 1).Value > 1000
tot = tot + .Offset(tmp, -1).Value
tmp = tmp + 1
Loop
.Offset(, 6).Value = tmp
.Offset(, 7).Value = tot
.Offset(, 8).Value = .Value
.Offset(, 9).Value = .Offset(tmp, 1).Value
.Offset(, 10).Value = 1
Else
.Offset(, 10).Value = 0
End If
rw = rw + tmp
Nextyou may need to change the guessed value of 1000 i used and change the offset of the target columns
test to see if it works as you want