Looking for the last row and have it fill the other column

Question

I am newbie in here in Stackoverflow and in the VBA Field. Actually I need some help with my code.

I have created a macro and it seems there is something missing in my code.

Scenario:

If A1 = 0.5 then in column B1 should answered FLAT
If A2 = 2 then in column B2 should answered as PER
If A3 = 5 then in column B3 should answered as PER
If A4 = 0.1 then in column B4 should answered FLAT
If A5 = 0.2 then in column B5 should answered FLAT

The code that I have only looking for one cell. What I want is in column B my code will apply until the last row of column "A" that has data

Sub exe()
    Dim number As Integer, result As String

    number = Range("a1").Value

    If number <= 1 Then
        result = "Flat"
    Else
        result = “Per”
    End If

    'enter code here
    Range("b1").Value = result
End Sub

if your number can be `0.5` do not try to hold that in an integer which will will remove the decimal `Dim Number as Double` — Scott Craner, Mar 06 '20 at 16:04
I put it as a range("C3").value Its just a scenario...to get easily understand I also include the code above — Kate Sayson, Mar 06 '20 at 16:05
Yes I can do that But what I want is I want to add this in my excel automation where the system can identified for its perspective number and each string so if this is <= 1 then the answer is FLAT so if this is greater than 1 then the answer is PER — Kate Sayson, Mar 06 '20 at 16:15
You can use worksheet_change event to check for the value entered at realtime in Col A to update values in Col B — Siddharth Rout, Mar 06 '20 at 16:17
Oh! actually I could not do that why? if the 1 worksheet will someone will touch or change something the formula all of that will get destroy that is why I want to make it in macro — Kate Sayson, Mar 06 '20 at 16:20
No it will not get destroyed. It will do exactly what your macro would do — Siddharth Rout, Mar 06 '20 at 16:22
but is it possible that my question will get work? refer to body msg of this question — Kate Sayson, Mar 06 '20 at 16:30
I gave you all 3 solutions in my answer below. take your pick :) — Siddharth Rout, Mar 06 '20 at 16:32
Hi @SiddharthRout what does it mean by relevant Sheet i got an error on this ,,, BTW Thank you for the codes. but I try the last one. but got an error — Kate Sayson, Mar 06 '20 at 16:59
Same question as https://stackoverflow.com/questions/60565181/fill-specific-text-for-a-specific-column/60566813?noredirect=1#comment107153169_60566813 — Error 1004, Mar 06 '20 at 18:01

Siddharth Rout · Answer 1 · 2020-03-06T16:37:00.920

I would recommned using a formula

=IF(TRIM(A1)="","",IF(OR(A1=0.1,A1=0.2,A1=0.5),"FLAT",IF(OR(A1=2,A1=5),"PER","")))

But since you mentioned in comments

so if this is <= 1 then the answer is FLAT so if this is greater than 1 then the answer is PER – Kate Sayson 16 mins ago

So the above will change to

=IF(TRIM(A1)="","",IF(A1<1,"FLAT",IF(A1>=1,"PER","")))

Other alternative is to use the worksheet change event. This will only act when a value in Column A changes and will only update Column B. So your rest of the worksheet is safe.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range

    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Columns(1)) Is Nothing Then
        For Each aCell In Target
            If IsNumeric(aCell.Value2) And _
               Len(Trim(aCell.Value2)) <> 0 And _
               aCell.Column = 1 Then
                Select Case aCell.Value2
                    Case Is < 1: Range("B" & aCell.Row).Value = "FLAT"
                    Case Else: Range("B" & aCell.Row).Value = "PER"
                End Select
            End If
        Next
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

If you still want a macro then try this

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long
    Dim i As Long

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Find last row in Col A
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> Loop through relevant cells in Col A
        For i = 1 To lRow
            If IsNumeric(.Range("A" & i).Value2) And _
               Len(Trim(.Range("A" & i).Value2)) <> 0 Then
                Select Case .Range("A" & i).Value2
                    Case Is < 1: Range("B" & i).Value = "FLAT"
                    Case Else: Range("B" & i).Value = "PER"
                End Select
            End If
        Next i
    End With
End Sub

Thank you for the codes @siddharth but still i got an error. — Kate Sayson, Mar 06 '20 at 17:03

Looking for the last row and have it fill the other column

1 Answers1