CODEWITHSUNDEEP
excelvba
Mohamed Abubakkar
Mohamed Abubakkar

Reputation: 71

Fill Range with Formulas

I would like to put some formula which has to be like this

sheet3.a1.value = "=sheet2!a1=Sheet1!a1"

I am trying to achieve this through VBA with using static value

Please find the below code

Sub test()
    Dim RowValue, ColumnValue As String

    RowValue = InputBox("Enter Row Number")
    ColumnValue = InputBox("Enter Column Number")

    RangeValue = RowValue + ColumnValue

    Sheet3.Range("A1:" & RangeValue).Value = "=Data1!" & "Range" & "=Data2!" & "Range"
End Sub

Upvotes: 1

Views: 72

Answers (4)

Sebastiaan35
Sebastiaan35

Reputation: 31

The following code will query row and column number and enter the hard-coded string in the desired cell:

Sub test()
    Dim RowValue, ColumnValue As Long
    Dim RangeValue As String

    RowValue = InputBox("Enter Row Number")
    ColumnValue = InputBox("Enter Column Number")

    Worksheets("Sheet3").Cells(RowValue, ColumnValue).Value = "=Data1!" & "Range" & "=Data2!" & "Range"
End Sub

Upvotes: 0

Error 1004
Error 1004

Reputation: 8230

You could try:

Option Explicit

Sub test()

    Dim RowValue As Long, ColumnValue As Long, RangeValue As Long

    RowValue = InputBox("Enter Row Number")

    ColumnValue = InputBox("Enter Column Number")

    Sheet3.Range(Cells(1, 1), Cells(RowValue, ColumnValue)).Formula = "=Data1!A1=Data2!A1"

End Sub

Upvotes: 0

user11408315
user11408315

Reputation:

If you are filling a range with formulas, the relative (not absolute) formulas will adjust the formula relative to its origin position.

Sub test()

    Dim RowValue as LONG, ColumnValue As String, RangeValue As String

    RowValue = InputBox("Enter Row Number")
    ColumnValue = InputBox("Enter Column LETTER")
    RangeValue = ColumnValue & RowValue 

    Sheet3.Range("A1:" & RangeValue).FORMULA = "=Data1!A1=Data2!A1"

End Sub

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149335

Do not use InputBox. Use Application.InputBox. It let's you to specify Type:=

Is this what you are trying (Untested)?

Sub test()
    Dim RowValue As Long, ColumnValue As String
    Dim rng As Range

    '<~~ Type:=1 ==> Numeric Value
    RowValue = Application.InputBox(Prompt:="Enter Row Number", Type:=1) 

    '<~~ Type:=2 ==> TextValue
    ColumnValue = Application.InputBox(Prompt:="Enter Column Number", Type:=2) 

    '~~> Check if the user entered valid values
    On Error Resume Next
    Set rng = Range(ColumnValue & RowValue)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "Wrong parameters specified"
        Exit Sub
    End If

    Sheet3.Range("A1:" & rng.Address).Formula = "=Data1!" & _
                                                rng.Address & _
                                                "=Data2!" & rng.Address

End Sub

Upvotes: 1

Related Questions