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

Question

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

FunThomas · Accepted Answer · 2019-01-28 10:28:30Z

1

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.

answered Jan 28, 2019 at 10:28

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

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

1 Comment

QHarr Over a year ago

be careful with isempty versus empty string literals +

Skin · Accepted Answer · 2019-01-28 10:24:36Z

0

"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.

answered Jan 28, 2019 at 10:24

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related