Prev: Macro help needed
Next: HELP: Email body transcript deleted when receiving response emails
From: Rick Rothstein on 25 Mar 2010 14:55
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"
<MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message
news:92920C94-A24F-4598-A145-DDE8FB1257EE(a)microsoft.com...
> 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.

From: Rick Rothstein on 25 Mar 2010 15:08
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"
<MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message
news:0A32311E-3ADE-4771-B419-CC02FD5A2D72(a)microsoft.com...
> 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").
>
> "tompl" wrote:
>
>> Try this:
>> mynum = Worksheets("Sheet1").Range("A4").Value
>>
>> Tom
>>
>>
>> "MovingBeyondtheRecordButton" wrote:
>>
>> > 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.

From: tompl on 25 Mar 2010 15:12
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" wrote:

> 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").
>
> "tompl" wrote:
>
> > Try this:
> > mynum = Worksheets("Sheet1").Range("A4").Value
> >
> > Tom
> >
> >
> > "MovingBeyondtheRecordButton" wrote:
> >
> > > 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.
From: MovingBeyondtheRecordButton on 25 Mar 2010 15:41
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

"Don Guillett" wrote:

>
> 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
> dguillett(a)gmail.com
> "MovingBeyondtheRecordButton"
> <MovingBeyondtheRecordButton(a)discussions.microsoft.com> wrote in message
> news:92920C94-A24F-4598-A145-DDE8FB1257EE(a)microsoft.com...
> > 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.
>
> .
>
From: MovingBeyondtheRecordButton on 25 Mar 2010 15:46
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.


"tompl" wrote:

> 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" wrote:
>
> > 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").
> >
> > "tompl" wrote:
> >
> > > Try this:
> > > mynum = Worksheets("Sheet1").Range("A4").Value
> > >
> > > Tom
> > >
> > >
> > > "MovingBeyondtheRecordButton" wrote:
> > >
> > > > 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.
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Macro help needed
Next: HELP: Email body transcript deleted when receiving response emails