0

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
Improve this question
4
  • 1
    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. Commented Jan 24, 2021 at 8:20
  • 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. Commented Jan 24, 2021 at 8:23
  • 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. Commented Jan 24, 2021 at 8:28
  • @ VBasic2008 Thank you i edited. Commented Jan 24, 2021 at 8:33

2 Answers 2

Reset to default
0

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Thank you sooo much @FaneDuru as always. I just need one more thing that is if Sheet15.ColB values is empty then code should paste 0 in the Sheet4 range
@Learning: Then, try testing the updated code.
this error is occur, pasteboard.co/JL6oK5S.png
@Learning: This is happening because you did not correctly copy the above code... "" is not the same with 0 (zero). You should use WorksheetFunction.CountA(Sheet15.Range("A:A")) = 0. The same in the above line using WorksheetFunction.CountA(...). It should return a Long, not an empty string... You must learn a little more about the variable declarations and their meaning.
@Learning: Glad I could help! But, doing what you initially requested, it should not work because of the header in "B1", which will not let CountA returning zero...
|
0

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
Improve this answer

6 Comments

But it is not updating the values in Sheet4.Range("E" & Sheet4.Rows.Count).End(xlUp).Offset(1, 1), Sheet15 value is in % that will be copy and paste
If the value in B7 is 15, then you have to use 15/100 i.e. cel1.Value / 100, to find 15% in rng15. Or format B7 also as percentage, but make it 100 times smaller.
Let me update my question once again with pictures.
@ VBasic2008 updated my question with pictures please have a look
Sheet1.Range("B7") is Sheet1.Range("B4") in the image. Have you changed that?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.