PDA

View Full Version : Solved: Loop to Get and put values



sujittalukde
05-07-2008, 04:08 AM
I am using the following code to take the value in a variable and put information by looping.
But there is certain some problems in looping so I am not getting the desired result.
Can someone please help me to rectify the code.
Also please note that I cant use formula as this is a part of my project where code has to do the job.


Sub nameandackn()
Dim Fname As Boolean
Fname = False
qtr = 1
For i = 1 To 9
Range("a" & i).Select
'Do
ename = Range("A" & (10 + qtr)).Value
If ActiveCell.Value = ename And ActiveCell.Offset(0, 2).Value = qtr Then
ackn = ActiveCell.Offset(0, 3).Value
Range("C" & (10 + qtr)).Value = ackn
Else
Do
ActiveCell.Offset(1, 0).Select
Loop Until (ActiveCell.Offset(1, 0).Value = ename And ActiveCell.Offset(0, 2).Value) Or ActiveCell.Offset(1, 0).Value = ""
ackn = ActiveCell.Offset(0, 3).Value
Range("C" & (10 + qtr)).Value = ackn
End If
qtr = qtr + 1
'Loop Until ActiveCell.Offset(1, 0).Value = "" Or Fname = True
Next i
End Sub

Edited by Simon Lloyd: Replaced Code tags with VBA tags
A sample file is attached for ready reference.

Bob Phillips
05-07-2008, 04:36 AM
Which sheet is supposed to be the sheet to work on?

And have you people never heard of indenting your code, it makes life so much easier.

david000
05-07-2008, 09:25 PM
Also please note that I cant use formula as this is a part of my project where code has to do the job.


Be that as it may, that should not be a huge negitive issue here. In this case posting formulas may help you clarify your problem and help other readers solve your issue.

For example, you can say, "I need to replicate this formula in code, etc."

sujittalukde
05-08-2008, 01:53 AM
Thanks for the replies. In the mean time I solved the same with this code.



Sub nameandackn1()
Range("A1").Select
'Range("C13:C16").Select
Selection.ClearContents
Srow_I = 13
For i = Srow_I To 16
ename = Range("a" & i).Value
q = Range("B" & i).Value
For j = 1 To 10
Range("A" & j).Select
ActiveCell.Offset(1, 0).Select
If ActiveCell.Value = ename And ActiveCell.Offset(0, 2).Value = q Then
akn = ActiveCell.Offset(0, 3).Value
Range("a" & i).Offset(0, 2).Value = akn
End If
Next j
Next i
End Sub

david000
05-08-2008, 09:20 AM
Sub nameandackn1()
Dim i, j As Integer, ename, q As Variant

For i = 13 To 16
ename = Cells(i, "A").Value
q = Cells(i, "B").Value
For j = 1 To 10
If Range("A" & j) = ename And Range("C" & j) = q Then
Range("C" & i).Value = Range("D" & j).Value
End If
Next j
Next i
End Sub

sujittalukde
05-08-2008, 10:05 PM
Thanks.