Need some help whith vba [Closed]

Report
-
 Da helpzor -
Hello,
i have created a loop that loops thro column B and testing data up agenst seartan criterias, if they match it copys the data to a spesefied location. but i have one problem , what i am struggeling with is, When the loop has run through column B and chekd the data up agenst cell J9 and I10 i then want it to chek the data up against cell K9 and I11 and then L9 and I12 so on.

i have tryd wit offcet(0, k) and then make the k value incrise ewery time the loop has run through column B but im unble to increase the K value. :/

it is alo important to notice that the amount of data will newer be the same and it is imposible to know how many rows and column's i wil have.

im a beginner at Vba and im gratefulf for any help.

4 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello Da helpzor

Instead of using Range("I10") use cells(9,y) where y=10,11,... and change all cell references to this structure and increase y=y+1 after 1 iteration until activesheet.usedrange.rows.count
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hello Da helpzor

To get the no. of rows & columns use
activesheet.usedrange.rows.count
activesheet.usedrange.columns.count
hey there. i may be just me but i dont see wath you posted is gonna help me : /

here is the code i got so far (yeh it is at an exprimental stage and im a beginner so it may look loco) as i posted i want it to be able to not onlye teste the content of column B10 agenst J9 and I10 but i want it to be able to cahange the cells it test the content agenst.

hope it is possible to understant waht im trying to achive.


Sub Loop1()
Range("B10").Select
Dim k As Integer
k = 0


For x = 1 To 100 Step 1
If ActiveCell = Range("J9").Offset(0 & k) And ActiveCell.Offset(0, 2) = Range("I10").Offset(k & 0) And ActiveCell.Offset(0, 3).Value > Range("j10").Offset(0 & k).Value Then
ActiveCell.Offset(0, 3).Copy
Range("J10").PasteSpecial
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If

If IsEmpty(ActiveCell) Then
Range("B10").Select
End If
Next x


End Sub
thanks a lot for the help i made it worke exactely the way i wanted ;)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!