trouble declaring a variable as a specific cell on a worksheet

Question

Very new to VBA. i am having trouble declaring a variable as a specific cell on a worksheet.

I have tried defining the cell by rows and columns but when I put a watch on the line it says Value is out of context. The variable is testname and it is in cell E2 of the worksheet I have set as the variable raw.

Sub findcomponents()

    Dim raw As Worksheet
    Dim res As Worksheet
    Dim temp As Worksheet
    Dim testname As String
    Dim finalrow1 As Integer
    Dim finalrow2 As Integer
    Dim i As Integer



    Set raw = Worksheets("rawdata")
    Set res = Worksheets("resultcomponents")
    Set temp = Worksheets("uploadtemplate")

    testname = raw.Range("E2").Value


    finalrow1 = raw.Range("A10000").End(xlUp).Row
    finalrow2 = res.Range("A10000").End(xlUp).Row

    For i = 2 To finalrow2
        If res.Cells(i, 4) = testname Then
            Range(Cells(i, 2), Cells(i, 4)).Copy
            temp.Range("A10000").End(xlUp).Cells("A2").Paste
        End If
    Next i

    End Sub

I expect the value to be the string in the E2 cell Edit: I added the rest of the code. When I run it doesn't do anything. It is supposed to take the string testname and loop through a list on the res worksheet and return the matches. I put a watch on the testname line because i thought it would show me that it was comparing the correct string and the Value in the watch line says

yes the paste line is incorrect. I also tried temp.Range("A10000").End(xlUp).Offset(1, 0).Paste and this makes it angry also.

The fix works with an edit on the worksheet name. But there is more than 1 match on the res worksheet. That is why I thought finding the the last row (but I should have offset 1 row) would return all the matches. This does work to return all values. temp.Range("A10000").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Is there a better way to do this not using Range("A10000").End(xlUp)?

"Value is out of context" doesn't sound like a VBA error message. What's the exact error you get, and on which line? — Comintern
This looks like an error in the testing procedure. Add this line at the bottom of your code and run it again. MsgBox "E2 = " & testname — Variatus
what @KenWhite said. The error isnt the testname Step thru pressing F8 which line is the error on? — alowflyingpig

urdearboy urdearboy · Accepted Answer · 2019-02-12T02:43:34

Your key problem is with the paste statement.

You can use one of the two following methods to paste (although it should be noted that there are others)

Range.Copy PasteRange

or

Range.Copy
PasteRange.PasteSpecial (paste type (values, formats, etc.))

This has also been updated with more standard variable blocks/variable names for Last Row calculations. Also, notice that I have also updated the Last Row calculation to be a little more dynamic. Lastly, swapping Integer for Long

Option Explicit

Sub findcomponents()

Dim raw As Worksheet: Set raw = ThisWorkbook.Sheets("rawdata")
Dim res As Worksheet: Set res = ThisWorkbook.Sheets("resultcomponents")
Dim temp As Worksheet: Set temp = ThisWorkbook.Sheets("uploadtemplate")

Dim testname As String
Dim LR1 As Long, LR2 As Long, LR3 As Long, i As Long

LR1 = raw.Range("A" & raw.Rows.Count).End(xlUp).Row  '<-- This variable is never used??
LR2 = res.Range("A" & res.Rows.Count).End(xlUp).Row  '<-- Updated for standard Last Row (LR) calculation

For i = 2 To LR2
    If res.Cells(i, 4) = raw.Range("E2") Then
        res.Range(res.Cells(i, 2), res.Cells(i, 4)).Copy  '<-- Qaulified Ranges!!
        LR3 = temp.Range("A" & temp.Rows.Count).End(xlUp).Offset(1).Row
        temp.Range("A" & LR3).PasteSpecial xlPasteValues '<-- Correct Paste Method
    End If
Next i

End Sub

The naming conventions used are just my preference. To an extent, you are free to name variables however you see fit

trouble declaring a variable as a specific cell on a worksheet

1 Answers

Your key problem is with the paste statement.