Find cell position using lastRow to enter value

Question

I am going to try and explain this as best as I can in English.

I want to enter a variable as a time value in a particular cell in excel. I have the time variable calculation sorted. It is finding the specific cell to enter that variable into that I have a difficulty with.

The destination cell where I want the time value to be placed is determined as an offset from a Lastrow function. e.g.:

If Range("AC1").Value = 30 Then
        LastRow.Offset(16, 1).Value = TextBox2.Text
 Else If Range("AC1").Value = 33 Then
     LastRow.Offset(20, 1).Value = TextBox2.Text
  etc. etc

So, If I Dim Rng As Range and lets say ("AC1").Value = 30

How do I get the value that has been entered into "TextBox2.Text" to find its way to the LastRow.Offset(16, 1) position or if the variable where anything else, how to find the specific offset that corresponds to that position?

I really hope this question makes sense.

Hi, Clearly my cutting and pasting has not worked and I just don't have the knowledge to figure out which bit of code is causing the problem I enter a 3 or 4 digit number in Textbox2. Validation is done (Works) Value of "AC1" is determined to determine offset paste placement of value in spreadsheet. (Works) Last column is determined according to Battery range name in spreadsheet. (Works) NOW I want to enter the value of time in a cell, offset from that "LastColl" position, according to the "iOffset" value. I also want to format this value as minutes and seconds. I have fiddled with the code and my thinking was to range define as "TimeCell" the time entry cell on the spreadsheet so that I can paste the time value to a specific range Now the problem is, pasting the TextBox2 value into that cell.

Dim tbV As String
Dim sV As String
Dim mV As String
Dim TimeCell As Range
Dim LastColl As Range

tbV = TextBox2.Text

    If Len(tbV) > 4 Or Len(tbV) < 2 Or Not IsNumeric(tbV) Then
        MsgBox "wrong"
    Exit Sub
    End If


sV = Right(tbV, 2)
mV = Left(tbV, Len(tbV) - 2)


Dim iOffset As Integer
    Select Case Range("AC1").Value
        Case Is = 30: iOffset = 16
        Case Is = 33: iOffset = 20
        Case Is = 22: iOffset = 14
    End Select

Set LastColl = Range("Battery" & BatteryNumber).End(xlToRight)

Set TimeCell = Range(LastCol.Offset(iOffset, 0))

TimeCell.NumberFormat = "m:ss"
TimeCell.Value = TimeSerial(0, Val(mV), Val(sV))

Are you trying to find the last row in column "AC"? Like Range("AC1").End(xlDown) or Range("AC" & Range("A1").SpecialCells(xlCellTypeLastCell).Row)? Or something of that sort? — Demetri
I am finding the last column in a row by starting in Coloum a and then Set LastRow = Sheet1.Range("Battery" & BatteryNumber).End(xlToRight) — Rob D
I am finding the last column in a row by starting in a named range on the spreadsheet and then Set LastRow = Sheet1.Range("Battery" & BatteryNumber).End(xlToRight) This is my starting point from where I enter the values in various cells in that column with different offsets. eg. LastRow.Offset(20, 1).FormulaR1C1 = "=R[3]C*" & TextBox3.Text. The difficulty I am having is now doing an If - else if procedure that determines a specific offset position from this LASTROW position based on the variable that I have in cell AC1. — Rob D

Scott Holtzman Scott Holtzman · Accepted Answer · 2016-01-05T16:21:34

Instead of a bunch of If Then Else statements, Select Case may be easier here:

Dim iOffset as Integer
Select Case Sheet1.Range("AC1").Value2 'I assume it's Sheet1, but change as needed
    Case Is = 30: iOffset = 16
    Case Is = 33: iOffset = 20
    Case Is = ??: iOffset = ??
    '... keep going
End Select

Dim LastRow as Range
Set LastRow = Sheet1.Range("Battery" & BatteryNumber).End(xlToRight) 
'interesting that you call it `LastRow`, but it refers to the last **column**

Sheet1.LastRow.Offset(iOffset,1).Value = TextBox2.Text

Find cell position using lastRow to enter value

1 Answers