Need some help whith vba

Closed
Da helpzor - Jul 16, 2009 at 03:08 AM
 Da helpzor - Jul 21, 2009 at 01:22 AM
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

Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Jul 20, 2009 at 11:28 PM
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
Excelguru
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Jul 17, 2009 at 05:15 AM
Hello Da helpzor

To get the no. of rows & columns use
activesheet.usedrange.rows.count
activesheet.usedrange.columns.count
0
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
0
thanks a lot for the help i made it worke exactely the way i wanted ;)
0