CODEWITHSUNDEEP
vbaexcel
Atluri jaya teja
Atluri jaya teja

Reputation: 11

How to write data into cells in VBA with run time locations?

I have a user defined function in VBA, which write the some Data calculated by a Function. The location to write data will be the row-cells next to "cell" where user calls the VBA function in Excel sheet.

ex: consider printing a multiplication table for a number Given by user 

Public function Mul_Table(number as integer)
   dim wb as string
   dim ws as string
   dim i as integer ,row_num as integer,column_num as integer 
   wb= activeworkbook.name
   ws=activesheet.name
   row_num = activecell.row
   column_num =activecell.column
   for i = 1 to 20
        workbooks(wb).sheets(ws).cells(row_num+i , column_num).value = i*number
   next i
end function

Now when I executed I got problem as #value error in the cell where the function is called. In debugging, the controller is throwing an error at line "workbooks(wb).sheets(ws).cell(row_num+i , column_num) = i*number" but I am not able to find why it is going wrong.

Upvotes: 1

Views: 3152

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6216

For what you are doing, you could using the worksheet_change event which will fire when something is entered, you can then have the code check the address of target (Target.Address) to see if they entered the data where you want, if so then perform your task.

Here is an example I just knocked up for you:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    For X = 1 To 20
        Range("B" & X).Value = Range("A1").Value * X
    Next
    Application.EnableEvents = True
End If
End Sub

Drop that in the VBE at the worksheet level and then enter a number in A1.

There is a number of other ways you can do this, you can post the entire range in one go rather than looping through (I left the looping as you said you are learning and this is how you have tried to do it)

There is no error checking in there or anything but I am sure you could figure that part out based on what criteria you want.

Alternatively you can do this with formulas something like in B1:

=$A$1*ROW(B1)

Then drag down and put a number in A1

Upvotes: 0

Kannan Suresh
Kannan Suresh

Reputation: 4580

You cannot use a function or sub routine to do this. You can add a button or shortcut to excel to run your code. I would like to make your code more optimized.

  1. Sub routine to generate multiplication table.

    
Public Sub Mul_Table(number As Integer)
   Dim i, row_num, column_num As Integer
   For i = 1 To 20
       ActiveCell.Offset(i, 0).Value = i & " x " & number & " = " & i * number
       'ActiveCell.Offset(i, 1).Value = i * number 'Uncomment if you want the result in a new column
   Next
End Sub

  2. Sub routine that generate multiplication table based on user input.

    
Sub getMultiplicationTable()
   mynumber = InputBox("Enter the number.", "Enter number")
   Call Mul_Table(CInt(mynumber))
End Sub

  3. Sub routine that generates multiplication table of 2

    
Sub getMultiplicationTableOfTwo()
   Call Mul_Table(2)
End Sub

Upvotes: 0

David Zemens
David Zemens

Reputation: 53653

The location to write data will be the row-cells next to "cell" where user calls the VBA function in excel sheet .

This cannot be done with a User-defined function, called from the worksheet. That is why you're getting the #Value! error returned.

A function by definition is designed to return a value to the cell wherein the function resides. It should not be used to attempt to alter other cells.

If you breakpoint and step through you're code, you'll observe that it will abort probably on this line, if not sooner:

workbooks(wb).sheets(ws).cells(row_num+i , column_num).value = i*number

The reason for this, generally speaking, is that a UDF called from the worksheet is not permitted to manipulate any range objects (or properties of range objects) which are not explicitly passed as an argument to the function. This is to prevent circular errors, infinite loops, etc.

Make it a Sub instead of a function, and it should work:

Public Sub Mul_Table()
   dim number as INteger

   dim wb as string
   dim ws as string
   dim i as integer ,row_num as integer,column_num as integer 

   number = Application.InputBox("Enter an integer value")
   wb= activeworkbook.name
   ws=activesheet.name
   row_num = activecell.row
   column_num =activecell.column
   for i = 1 to 20
        workbooks(wb).sheets(ws).cells(row_num+i , column_num).value = i*number
   next i
End Sub

Upvotes: 2

Related Questions