need some help whith vba Closed

Question

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.

Excelguru · Answer

Hello Da helpzor

To get the no. of rows & columns use 
activesheet.usedrange.rows.count
activesheet.usedrange.columns.count

Da helpzor · Answer

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

Excelguru · Answer

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

Da helpzor · Answer

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

Need some help whith vba

4 responses

Similar discussions

Subscribe To Our Newsletter!