recording data at the last blank cell

[coolman]

hi

I have a syntax here to record data however it keep recording over the previous data. Could anyone advice me how to program that all new data is to be recorded at new row of blank cells without over write the previous entry?

Dim ws As Worksheet
Set ws = Worksheets("budget tracking")
Dim newrow As Long


If Me.CBBudgetCode.ListIndex <> -1 And Len(Me.TxtBoxPurchaseDate.Value) = 10 And Me.TxtBoxPORefNo.Value <> "" And Me.TxtBoxVendorName.Value <> "" And Me.TxtBoxQuantity.Value <> "" And Me.CBUnit.ListIndex <> -1 And Me.TxtBoxRate.Value <> "" And Me.CBLocation.ListIndex <> -1 And Me.TxtBoxTransctionDetails.Value <> "" Then


newrow = Application.WorksheetFunction.CountA(ws.Range("b:b")) + 6


ws.Cells(newrow, 2).Value = Me.CBBudgetCode.Value
ws.Cells(newrow, 3).Value = Me.TxtBoxPurchaseDate.Value
ws.Cells(newrow, 4).Value = Me.TxtBoxPORefNo.Value
ws.Cells(newrow, 5).Value = Me.TxtBoxVendorName.Value
ws.Cells(newrow, 6).Value = Me.TxtBoxItemPurchased.Value
ws.Cells(newrow, 7).Value = Me.TxtBoxQuantity.Value
ws.Cells(newrow, 8).Value = Me.CBUnit.Value
ws.Cells(newrow, 9).Value = Me.TxtBoxRate.Value
ws.Cells(newrow, 10).Value = Me.TxtAmount.Value
ws.Cells(newrow, 11).Value = Me.CBLocation.Value
ws.Cells(newrow, 12).Value = Me.TxtBoxTransctionDetails.Value
End If

 

[craigpritchardweb]

Could you not add an IF statement for each cell to see if the cell is empty?

For example ...

If Trim(ws.Cells(newrow, 2).value = "" Then
    ws.Cells(newrow, 2).Value = Me.CBBudgetCode.Value
End If

Do that for each of the cells you wish to check before updating. There may be more elegant solutions but that's the one I thought of in the shortest possible time.

 

[kvsrinivasamurthy]

Try this

newrow = Range("B" & rows.count).end(xlup).offset(1,0)

 

[coolman]

thank you for the suggestion. however it does not work

Try this

newrow = Range("B" & rows.count).end(xlup).offset(1,0)

 

[kvsrinivasamurthy]

Sorry. I have corrected

newrow = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row