If Then & Input Box

[ismokenov1991]

Hey there

I have an excel sheet with VBA macro with an Input Box. Every time the macro runs I want I will put a number or word inside it, but when I run it again I put it into the next raw. Everything`s fine I just got one problem, because I have only declared one variable "X" all the cells have same value.

Here`s the code

Sub getvalue1()
Dim x As String
 
x = InputBox("enter the name of the cell into the dialog box")
 
If x <> "" Then Range("a1").Value = x
 
If Range("A1").Value > 0 Then
 
Range("A1").Offset(1, 0).Value = x
 
End If
 
End Sub

It does not makes sense creating more than one variable, if let say I want to enter data for a thousand cells. I think I need to put something like not equal to or remain constant but it didnt work. Although its most probably something wrong with my code.

I would appreciate if someone could give me an answer.


Thansk!!!

 

[NARAYANK991]

Hi ,

I am not sure what you wish to do ; can you try this code and see if it is what you are looking for ?

Sub getvalue1()
    Dim x As String
    Static i As Integer
    x = InputBox("enter the name of the cell into the dialog box")
 
    If x <> "" Then
      Range("a1").Value = x
      If Range(x).Value > 0 Then
          Range("A1").Offset(i + 1, 0).Value = Range(x).Value
          i = i + 1
      End If
    End If
End Sub

Narayan