CODEWITHSUNDEEP
excelvba
Nic
Nic

Reputation: 5

VBA - How to output a variable to a particular row and column in excel?

This code should find the correct cell (in the column corresponding to it's 'length' and the next empty row) in which to output a variable.

I'm getting the error message:

method range of object _worksheet failed

on lines 13 onward containing "outputcolumn"

In the MsgBox lines, the correct column and row number are being displayed, so I am not sure why it is not happy with my outputcolumn in particular.

Private Sub OutputRowAndColumn()

    'Choose correct column: Find the length column and name this outputcolumn
    Dim cell As Range, outputcolumn As Integer
    Set cell = Range("FindLength").Find(Range("Length").Value, LookIn:=xlValues)
    If Not cell Is Nothing Then
        outputcolumn = cell.Column
    End If

    MsgBox "Output column is number " & outputcolumn & "."

    'Choose correct row: If the cell to the left of "cell" is empty then this is the first row of output otherwise find next empty cell down
    If Sheet1.Range(outputcolumn & "4").Offset(0, 1).Value = "" Then
        outputrow = 4 ''' error msg '''
    ElseIf Sheet1.Range(outputcolumn & "5").Offset(0, 1).Value = "" Then
        outputrow = 5
    Else
        outputrow = Sheet1.Range(outputcolumn & "4").Offset(0, 1).End(xlDown).Row + 1
    End If

    MsgBox "Output row is number " & outputrow & "."

    'Copy values 1, 2 and 3 from sheet 2 to sheet 1
    Sheet1.Range(outputcolumn & outputrow).Offset(0, 1).Value = Sheet2.Range("Value1").Value ''' error msg '''
    Sheet1.Range(outputcolumn & outputrow).Offset(0, 2).Value = Sheet2.Range("Value2").Value
    Sheet1.Range(outputcolumn & outputrow).Offset(0, 3).Value = Sheet2.Range("Value3").Value

End Sub

Upvotes: 0

Views: 1764

Answers (2)

FunThomas
FunThomas

Reputation: 29286

outputcolumn is a numeric value (you defined it as Integer, but you always should define variables holding row or column numbers as long to avoid overflow errors).

So let's say outputcolumn gets the number 2 (column B). You write Sheet1.Range(outputcolumn & "4"). To access a range by it's address, You would have to write something like Range("B4"), but what you write is Range(2 & "4"), which means Range("24"), and that is an invalid address for a Range.

You could try to translate the column number 2 to a B, but there is an easier way to access a cell when you know the row and column number: Simply use the cells-property:

If Sheet1.Cells(4, outputcolumn).Offset(0, 1).Value = "" Then
' (or)
If Sheet1.Cells(4, outputcolumn+1).Value = "" Then

Just note that the order of the parameters is row, column.

Upvotes: 1

Skin
Skin

Reputation: 11197

"outputcolumn" is numeric in your case and when using .Range(), it needs to be a proper alphanumeric cell reference like "C5", not all numeric.

I haven't tried it directly but changing this ...

If Not cell Is Nothing Then
    outputcolumn = cell.Column
End If

... to this ...

If Not cell Is Nothing Then
    outputcolumn = Split(cell.Address, "$")(1)
End If

... will go a long way to helping you.

Upvotes: 0

Related Questions