How to use the ISNULL/empty Function (VBA)

0
votes

Trying something like this. My code is find the value of cell B7 of Sheet1 in column A of Sheet15. If it is not found, do nothing. If it is found, write the value in the cell next to the found value to the next available cell in column F of Sheet4.

I have this number in Sheet1

enter image description here

Code will find that Sheet1 value in Sheet2 Col"A" then copy the Col"B" value that is "156".

enter image description here

After that code will paste that "156" VALUE IN Sheet3.Range("C2") to till where Col"B" used range end.

enter image description here

Looking forward to your help.

Dim lastR4  As Long

        lastR4 = Sheet4.Range("E" & Rows.Count).End(xlUp).Row
        If Sheet15.Range("A2:B2") Is Empty Then
        Exit Sub
        Else
       Sheet4.Range("F11:F" & lastR4).Value = Sheet15.Range("A" & _
                             WorksheetFunction.Match(Sheet1.Range("B7").Value, Sheet15.Range("A:A"), 0)).Offset(0, 1)

   End If
2
excelvba
You are trying to write the value from one cell Sheet15.Range("A" & whatever).Offset(, 1) to a range (an array) Sheet4.Range("F11:F" & lastR4).Value. Think about it for a while and then clarify what you want to write where. – VBasic2008
Hi, Actually i am begginer to VBA and unable to guess the thing what you are saying. @VBasic2008 I just want that if Sheet15 is empty then code should return empty. – Strenuous
Try something like this: The code is trying to find the value in cell B7 of Sheet1 in column A of Sheet15. If it is not found, do nothing (or return a message box). If it is found, write the value of the cell next to the cell of the found value to the next available cell in column F of Sheet4. Best do it in your post. – VBasic2008
@ VBasic2008 Thank you i edited. – Strenuous

2 Answers

1
votes

Lookup Using Application.Match

  • Using Application.Match is easier and cleaner then using WorksheetFunction.Match because you can test the result with IsError or IsNumeric.

The Code

Option Explicit

Sub test()

    ' The cell containing the lookup value.
    Dim cel1 As Range
    Set cel1 = Sheet1.Range("B7")
    
    ' First 'available' cell in column `E` column, but in column `F`.
    Dim cel4 As Range
    Set cel4 = Sheet4.Range("E" & Sheet4.Rows.Count).End(xlUp).Offset(1, 1)
    
    ' Range from cell `A1` to the last non-empty cell.
    Dim rng15 As Range
    Set rng15 = Sheet15.Range("A1", Sheet15.Range("A" & Sheet15.Rows.Count).End(xlUp))
    
    ' The index of the found value. If not found, returns error value.
    ' Therefore we use 'Variant'.
    Dim cMatch As Variant
    cMatch = Application.Match(cel1.Value, rng15, 0)
    
    ' Test if found with 'IsNumeric()'. You can also use 'Not IsError()'
    ' instead. Or even 'IsError()', but then switch the statements.
    If IsNumeric(cMatch) Then
        cel4.Value = rng15.Cells(cMatch).Offset(, 1).Value
        MsgBox "Value '" & cel1.Value & "' copied.", vbInformation, "Success"
    Else
        MsgBox "Value '" & cel1.Value & "' not found.", vbCritical, "Fail"
    End If

End Sub

EDIT

  • Adjust the sheets and cells appropriately.

Fill Range with Found Value

Sub test2()

    ' The cell containing the lookup value.
    Dim cel1 As Range
    Set cel1 = Sheet1.Range("B7")
    
    ' Range from cell 'E2' to the last non-empty cell, but column `F`.
    Dim rng4 As Range
    Set rng4 = Sheet4.Range("E2", Sheet4.Range("E" & Sheet4.Rows.Count) _
        .End(xlUp)).Offset(, 1)
    
    ' Range from cell `A1` to the last non-empty cell.
    Dim rng15 As Range
    Set rng15 = Sheet15.Range("A1", Sheet15.Range("A" & Sheet15.Rows.Count) _
        .End(xlUp))
    
    ' The index of the found value. If not found, returns error value.
    ' Therefore we use 'Variant'.
    Dim cMatch As Variant
    cMatch = Application.Match(cel1.Value, rng15, 0)
    
    ' Test if found with 'IsNumeric()'. You can also use 'Not IsError()'
    ' instead. Or even 'IsError()', but then switch the statements.
    If IsNumeric(cMatch) Then
        rng4.Value = rng15.Cells(cMatch).Offset(, 1).Value
        MsgBox "Value '" & cel1.Value & "' copied.", vbInformation, "Success"
    Else
        MsgBox "Value '" & cel1.Value & "' not found.", vbCritical, "Fail"
    End If

End Sub
1
votes

Try the next slightly adapted code, please:

Sub testFillDat()
    Dim lastR4 As Long
    
    lastR4 = Sheet4.Range("E" & rows.count).End(xlUp).row
    If WorksheetFunction.CountA(Sheet15.Range("A2:B2")) = 0 Then
        Exit Sub
    Else
        If Sheet15.Range("B2") = "" Then
            Sheet4.Range("F11:F" & lastR4).Value = 0
        Else
            Sheet4.Range("F11:F" & lastR4).Value = Sheet15.Range("A" & _
                WorksheetFunction.match(Sheet1.Range("B7").Value, Sheet15.Range("A:A"), 0)).Offset(0, 1)
        End If
    
    End If
End Sub

If there is no match of "B7" (Sheet1) in "A:A" (Sheet15) the code will raise an error. It can be adapted to catch it, but I only tried to show you how checking if a range with multiple cells is empty... In rest, your code should work.