Printable View
I need to see a way to do the following.
I have a 2 txtboxes on a spreadsheet that when cmd button is clicked, it moves to the next available row.
I need to have a vb script that will look at last row with data and if
Lastrow with Data
column A = Txtbox1.value & column C = Txtbox2.value Then
Just add Txtbox1.value in next available row
Else
Txtbox1.value & TxtBox2.value Can both go into next available row.
Any help would be great.
Try:
[VBA]Sub dural()
Dim r As Range
Dim nLastRow As Long, nNextRow As Long
ActiveSheet.UsedRange
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.Count + r.Row - 1
nNextRow = nLastRow + 1
If Cells(nLastRow, "A").Value = txtbox1.Value And Cells(nLastRow, "C") = txtbox2.Value Then
Cells(nNextRow, "A").Value = txtbox1.Value
Else
Cells(nNextRow, "A").Value = txtbox1.Value
Cells(nNextRow, "C").Value = txtbox2.Value
End If
End Sub
[/VBA]
That doesn't seem to work.
[vba]
If Cells(nLastRow, "A").Value = OLEObjects("Txtbox1").Object.Value And _
Cells(nLastRow, "C") = OLEObjects("Txtbox2").Object.Value Then
Cells(nNextRow, "A").Value = OLEObjects("Txtbox1").Object.Value
Else
Cells(nNextRow, "A").Value = OLEObjects("Txtbox1").Object.Value
Cells(nNextRow, "A").Value = OLEObjects("Txtbox2").Object.Value
End If
[/vba]
Coding looks good, i don't know why it doesn't work. Not sure if it's due to it being OLEObjects
I was able to get something working, but get an error if no values are present.
[vba]
Dim iRow As Long
Dim ws As Worksheet
Dim r As Range
Dim lRowN As Long
Dim lRowB As Long
Set ws = Worksheets("Capture")
lRowN = ws.Cells(Rows.Count, 1).End(xlUp).Value
lRowB = ws.Cells(Rows.Count, 3).End(xlUp).Value
If lRowN = ws.OLEObjects("TxtBox1").Object.Value And _
lRowB = ws.OLEObjects("TxtBox2").Object.Value Then
[/vba]
If there is a value in Row 2 it works the way I want it, but if no values in columns I get
Run-time error '13': Type Mismatch at
lRowN = ws.Cells(Rows.Count, 1).End(xlUp).Value
Fixed the issue
[VBA]Dim lRowN As String
Dim lRowB As String[/VBA]
This fixed the problem.