CODEWITHSUNDEEP
excelvba
b_dawg
b_dawg

Reputation: 23

Excel VBA paste a value if nothing is found

I want to go into a sheet and look for a value. If the value is there I want to grab the data in its row and paste transposed in another sheet. This function is working.

However, if the value in the column is not there I want to paste filler text into the column where the data would otherwise go.

I am getting a "Type Mismatch" error when I run the following code. What is going wrong/ how can I made this happen.

Dim c As Long

Windows("WBGrab").Activate '-> opens doc we want to look a

c = Sheets("SheetName").Columns(2).Find(What:="Commercial Income", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Row


If c Is Nothing Then
Windows("WBPaste").Activate
Range("C2:C7").Value = "-"

Else
Sheets("SheetName").Range("C" & c & ":H" & c).Select '-> opens MBI DSCR sheet and copes naming & values
Selection.Copy '-> copies the selected area
Windows("WBPaste").Activate '-> opens back up the data lake
Sheets("Sheet1").Range("C" & 2).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=True
        
End Sub

Upvotes: 0

Views: 99

Answers (1)

Tim Williams
Tim Williams

Reputation: 166790

If Find() fails to make a match then your code will error before getting to If c Is Nothing Then because of the .Row tagged onto the Find() line.

Something like this should work:

Sub Tester()
    
    Dim c As Range, wsSrc As Worksheet, wsDest As Worksheet, cDest As Range
    
    'set up source and destination sheets
    Set wsSrc = Workbooks("WBGrab").Worksheets("SheetName") 'add the file extension to the name
    Set wsDest = Workbooks("WBPaste").Worksheets("Sheet1")
    
    Set c = wsSrc.Columns(2).Find(What:="Commercial Income", LookIn:=xlValues, _
                                  LookAt:=xlPart, MatchCase:=False)
    
    Set cDest = wsDest.Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) 'next paste destination. C2?
    
    If c Is Nothing Then                     'didn't make a match with Find() ?
        cDest.Resize(1, 7).Value = "-"       'fill placeholders
    Else
        c.Offset(0, 1).Resize(1, 7).Copy     'got match - copy range
        cDest.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
              Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End If
        
End Sub

Upvotes: 1

Related Questions