Please help

[shahji]

Hello all,

I am trying to write a code. I need the code to do the following:

An input box asks for a number,
Then the below code divides values in a given column and paste them in the next available empty column. Then the 2nd formula runs and it places the values in the next available column.

Now this routine should repeat as many time as the number in the input box.

I have written/recorded the following but dont know how to add the input box which should do as i explained above.

Sub Analysis()

  
    rowcounter = Intersect(ActiveSheet.UsedRange, Columns("B:D")).Rows.Count
      
   For i = 18 To rowcounter
        
        Range("E" & i).FormulaArray = "=ROUNDDOWN((R34C4/2)/R34C4*RC[-3],0)"
        Range("F" & i).FormulaArray = "=ROUNDDOWN(RC[-1]*RC[-3],0)"
        If Range("B" & i) = "" And Range("C" & i) = "" Then
        Range("E" & i) = ""
        
       
        End If

    Next i
    

End Sub

Please let me know if you need any more information.

Thanks for help,

Shah.

[abhi.navale]

do u mean that u want to repeat ur code 'n' number of times in for,while or do while loop and you dont know how to take that value from textbox to function?

is it so?
if not can u plz explain in breif?

[shahji]

Thanks abhi,

I have been working on the code and i have come up with the following:

Sub AddData()

Dim i As Integer, j As Integer


rowcounter = Intersect(ActiveSheet.UsedRange, Columns("B:D")).Rows.Count
    
      myRange = Application.InputBox(prompt:="Beneficiaries", Type:=1)
               
       For i = 18 To rowcounter
        For j = 5 To myRange
         
         If Range("B" & i) = "" And Range("C" & i) = "" Then
                Range("E" & i) = ""
        
                Else
                Sheets("Investments").Cells(i, j).Value = "=ROUNDDOWN((R34C4/R1C5)/R34C4*RC2,0)"
                j = j + 1
                Sheets("Investments").Cells(i, j).Value = "=ROUNDDOWN(RC[-1]*RC3,0)"
                
                
         End If
        Next j
 
       Next i
       
End Sub

The code runs fine when i put value 1,2,3 in the input box but then it does not work afterwards. I need the code to run as many times as i say in the Inputbox, i.e. it could 1 time 2 or 10 times. It should put the formulae into the next empty box.

Is it also possible that the formulae i am using, i could also put the Inputbox value like below?

"=ROUNDDOWN((R34C4/InputBox)/R34C4*RC2,0)"

Many thanks,

Shah

[abhi.navale]

whats the data type of variable myRange?
I think this problem is arising because of incompatible variable data types. i.e. myRange , Range etc have diffrent data types that each other.
Try work around on this issue.

looking at the code I dont found any problem. It looks accurate.

You are doing this excel spreadsheet program in vb ? right?

[shahji]

Thanks Abhi,

Yes I am using excel VBA to write the code which is to be used on a spreadsheet.

I have tried a couple of things but the macro still would not insert formulae depending on the myRange number.

if i type in the Inputbox 1 or 2 it is fine but when i type in 3 it will still fill two columns and when the input box value is 4 it will insert formulae in 3 columns and so on. Dont know how to fix that problem.

Also as i asked before that how can i use the Inputbox value in the actual formula.

[abhi.navale]

dear,
i dont know much about excel to vb coding.
so please ask someone else.

sorry for the inconvinience.

[shahji]

No problem but thanks for trying :).