0

So I want to make a matlab/r-like tool in excel that mimics more code driven calculations than the more common excel worksheet applications typically contain

This workbook should contain:

  1. input sheet
  2. calculations sheet
  3. output sheet

I tried VBA, but I don't like it and also I have no experience coding in it (it takes too long for what I want to get)

So, my next try is to make it happen directly in Excel, without VBA.

For that goal, I need to:

  1. Create a table in the calculation sheet with the number of rows depending on the input variables given by the user in the input sheet.
  2. Fill up the cells of that table based on the input variables of the user, together with some pre-defined formules on what to put in those cells

E.g. user input 1 = 4, user input 2 = 6

Code =>> Create table with 4 rows and 4 columns, start from 6 in most left/top cell and multiple by 2 in each column, than before going to the next row, subtract two and do the same, etc.

so, output would be in this case

6 12 24 48

4 8 16 32


2 4 8 16


0 0 0 0

just as an example

You could easily program this if you knew the number of rows/columns beforehand, but what if you don't, and you want to create such a table?

Is this possible somehow? I don't want the end user to be doing anything else than providing the input values.

babipsylon
  • 103

1 Answers1

1

VBA solution. Assuming inputs are on B1 and B2

Public Sub CreateTable()

Dim rownum, colnum, startnum, z, multi As Integer
rownum = Range("B1").Value  'number of rows
colnum = Range("B1").Value  'number of columns

startnum = Range("B2").Value  'starting value

z = 3  'third row.

For x = 1 To rownum
    multi = 1   'use to multiply the starting value
    For y = 67 To 66 + colnum  '67 represents code for C

        Range(Chr(y) & z).Value = startnum * multi 'Set the value on the table
        multi = multi * 2

    Next y
    z = z + 1
    startnum = startnum - 2
Next x




End Sub

Demo

Reddy Lutonadio
  • 20,570