declaring active cell inside a for loop and offsetting from it

[l1075]

I have a macro in which the first column of the sheet is searched for
matching itemID. Then from there I want populate a series of textboxe
located in the row with the matching itemID. The length of the row
vary and I am trying to implement my code to
read until a blank cell

here is my code so far
Dim CellQuanValue
Dim CellDesValue
Dim ItemId = some id value
Counter = 1
Dim ItemList As Variant

ItemList = shtItemId.Range("A2:A100").Value
ItemList = shtItemId.Application.WorksheetFunction.Transpose(ItemList)
For i = 1 To UBound(ItemList)
If ItemList(i) = ItemId Then
'Set that cell as active cell???
Do
CellQuanValue = ActiveCell.Offset(0, Counter)
Me.Controls("txtQty" & Counter) = CellQuanValue
CellDesValue = ActiveCell.Offset(0, Counter+1)
Me.Controls("txtDes" & Counter) = CellDesValue
Counter = Counter + 1
'UntilNextCellIs Empty???
End If
Next i

Is there is a way to declare that cell as an active cell or an easie
way to to do this?

thank

 

[Tom Ogilvy]

Dim CellQuanValue
Dim CellDesValue
Dim ItemId = some id value
Counter = 1
Dim ItemList As Variant

ItemList = shtItemId.Range("A2:A100").Value
ItemList = shtItemId.Application.WorksheetFunction.Transpose(ItemList)
For i = 1 To UBound(ItemList)
If ItemList(i) = ItemId Then
Range("A2:A100")(i).Activate

Do
CellQuanValue = ActiveCell.Offset(0, Counter)
Me.Controls("txtQty" & Counter) = CellQuanValue
CellDesValue = ActiveCell.Offset(0, Counter+1)
Me.Controls("txtDes" & Counter) = CellDesValue
Counter = Counter + 1
While not isempty(activeCell.offset(0,counter))
Exit For ' a match has been made, on reason to keep looking
End If
Next i

Would be a guess at What you want.

 

[l1075]

thanks that seems like it will work

but now I am getting an "End If without Block If" Can I run a do loo
inside of a for loop?

heres my code

'Looking in the Sales Order Sheet
For i = 1 To UBound(OrderNum)
tempFlag = 0
If OrderNum(i) = purchase.Value Then 'Filter in Matching Order #
tempFlag = 1
If ItemId(i) = "" Then 'Test for Empty cell
tempFlag = 0
End If
End If

If tempFlag = 1 Then 'Filter out Empty cells
For j = 1 To UBound(ItemList)
If ItemList(j) = ItemId(i) Then
Range("A2:A100")(j).Activate
'Write beginning value for sub assembly
Me.Controls("txtSubQty" & Counter).Enabled
False
Me.Controls("txtQty" & Counter).Enabled = True
Me.Controls("txtSubQty" & Counter).BackColor
&H8000000F

Me.Controls("txtQty" & Counter).Value = Qty(i)
CellDesValue = ActiveCell.Offset(0, 0)
Me.Controls("txtItem" & Counter).Value
CellDesValue
CellDesValue = ActiveCell.Offset(0, 1)
Me.Controls("txtDes" & Counter).Value
CellDesValue
Counter = Counter + 1

Do
CellQuanValue = ActiveCell.Offset(0
SubCounter)
SubCounter = SubCounter + 1
Me.Controls("txtSubQty" & Counter).Enabled
True
Me.Controls("txtQty" & Counter).Enabled
False
Me.Controls("txtQty" & Counter).BackColor
&H8000000F
Me.Controls("txtSubQty" & Counter)
CellQuanValue
CellDesValue = ActiveCell.Offset(0
SubCounter)
Me.Controls("txtDes" & Counter) = CellDesValue
SubCounter = SubCounter + 1
Counter = Counter + 1
While Not IsEmpty(ActiveCell.Offset(0, Counter))
End If
Next j
Me.Controls("txtQty" & Counter).Value = Qty(i)
Me.Controls("txtItem" & Counter).Value = ItemId(i)
Me.Controls("txtDes" & Counter).Value = ItemDes(i)
Me.Controls("txtSubQty" & Counter).Enabled = False
Me.Controls("txtSubQty" & Counter).BackColor
&H8000000F
Counter = Counter + 1
End If
Next