Very Easy...Right?

[MovingBeyondtheRecordButton]

How do I change an existing code from...

mynum = Application.InputBox("Select Submission_ID")

....to mynum is located in cell A4 on sheet 1 (ie use the number already
located in A4) with no InputBox

I've tried....
mynum = Worksheets("Sheet1").Cells(R4,C1)
mynum = Worksheets("Sheet1").Cells(4,1).Value
mynum = Range("A4")

I've even tried...
Range("A4").Select
mynum = ActiveCell

I just don't know how to tell it to input the number already contained in A4
as the input for the already existing code.

 

[Gary Keramidas]

i'd use something like this

mynum = Worksheets("Sheet1").Range("A4").value


--


Gary Keramidas
Excel 2003


"MovingBeyondtheRecordButton"

 

[Don Guillett]

I see no reason that your second one would not work
mynum = Worksheets("Sheet1").Cells(4,1).Value

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"MovingBeyondtheRecordButton"

 

[tompl]

Try this:
mynum = Worksheets("Sheet1").Range("A4").Value

Tom

 

[MovingBeyondtheRecordButton]

Thanks...that works perfectly! Now that I have it working for one cell I
need to start working on making it loop for the Range("A44:A40").

 

[Rick Rothstein]

Your second one looks like it should have worked provided, of course, that
the name of your first sheet is Sheet1 (with no space between the "t" and
the "1"). As others have posted, you could have Range("A4") instead of
Cells(4,1)... I find the Cells form of referencing a range useful when
iterating either rows or columns in a loop.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"

 

[Rick Rothstein]

A44:A40 looks backwards for a range designation although Excel will
straighten it out to A40:A44). Anyway, you can try something like this...

For Each R In Range("A40:A44")

Using R (which should be declared as a Range variable) inside the loop to
reference each cell inside the range individually. You could also do this
(provided your cells are all in a single column)...

For X = 40 To 44

and use Cells(X, "A") to reference each individual cell in the range.

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"

 

[tompl]

There are many ways to create a loop, something like this might work:

Sub LoopIt()

Dim lng As Long

For lng = 40 To 44

Debug.Print Worksheets("Sheet1").Range("A" & lng)
'Your code goes here.
Next lng


End Sub

 

[MovingBeyondtheRecordButton]

You are right I must have not put Value on the end of that statement when I
originally tried it. I have the macro correctly pulling from our sql
database and putting the data in Sheet3 just like I wanted. Thanks.

What if I wanted to make the macro loop over the Range A4 to A40?

The procedure is...

For each number from each cell in range
Data is pulled from sql database
The output from each loop goes to Sheet3
Countif Calculations get performed
Data from Calculations go into Sheet2
Sheet3 gets cleared
Loop

 

[MovingBeyondtheRecordButton]

I don't understand this bit of code

Debug.Print Worksheets("Sheet1").Range("A" & lng)

What does it do? I don't want to print the data. I have the data output
into sheet3. Then the macro performs Countif calculations on that data I and
puts the results into sheet2. Sheet3 gets cleared then the macro repeats
until I have a table of data in sheet2.

 

[MovingBeyondtheRecordButton]

That was a typo...it should have said Range A4:A40

 

[tompl]

I was just using it to test my code. debug.print prints results in the
immediates window and is helpful when testing a macro. You can delete it but
it also demostrates the code for addressing each cell in the range.
Worksheets("Sheet1").Range("A" & lng) is how each cell is addressed and you
can use it to do whatever it is you want to do.

 

[MovingBeyondtheRecordButton]

I just wanted to say thank you...I have been stumped...I have needed the
code....

For Each R In Range("A4:A40")

The data is really in two columns.
Will it work if it is... For Each R In Range("A4:A40","F4:F40")?

 

[Rick Rothstein]

Yes, that For Each loop will work on a non-contiguous range (as long as the
order of iteration is not important), but you have not specified the Range
correctly for that non-contiguous range. As written, your range will equate
to A4:F40 because you specified each range as individual String values
separated by a comma... to have the Range evaluated as A4:A40 and F4:F40,
you need to write it as a **single** String value like this...

For Each R In Range("A4:A40,F4:F40")

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"

 

[MovingBeyondtheRecordButton]

Thanks again.

Yes, that For Each loop will work on a non-contiguous range (as long as the
order of iteration is not important), but you have not specified the Range
correctly for that non-contiguous range. As written, your range will equate
to A4:F40 because you specified each range as individual String values
separated by a comma... to have the Range evaluated as A4:A40 and F4:F40,
you need to write it as a **single** String value like this...

For Each R In Range("A4:A40,F4:F40")

--
Rick (MVP - Excel)



"MovingBeyondtheRecordButton"

.