PDA

View Full Version : Solved: Test Last Row Value before update



Emoncada
03-20-2013, 11:00 AM
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.

GarysStudent
03-20-2013, 11:23 AM
Try:

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

Emoncada
03-21-2013, 06:25 AM
That doesn't seem to work.


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



Coding looks good, i don't know why it doesn't work. Not sure if it's due to it being OLEObjects

Emoncada
03-21-2013, 01:51 PM
I was able to get something working, but get an error if no values are present.


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



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

Emoncada
03-21-2013, 02:28 PM
Fixed the issue

Dim lRowN As String
Dim lRowB As String

This fixed the problem.