Very Easy...Right?
in [Programming]
|
From: MovingBeyondtheRecordButton on 25 Mar 2010 15:52 That was a typo...it should have said Range A4:A40 "Rick Rothstein" wrote: > 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:56 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" wrote: > 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.
From: MovingBeyondtheRecordButton on 25 Mar 2010 16:00 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" wrote: > 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: Rick Rothstein on 25 Mar 2010 16:18 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(a)discussions.microsoft.com> wrote in message news:795D267D-6C98-4E85-A2A3-37E373C12C9F(a)microsoft.com... > 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" wrote: > >> 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: MovingBeyondtheRecordButton on 25 Mar 2010 17:25 Thanks again. "Rick Rothstein" wrote: > 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(a)discussions.microsoft.com> wrote in message > news:795D267D-6C98-4E85-A2A3-37E373C12C9F(a)microsoft.com... > > 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" wrote: > > > >> 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. > >> > >> . > >> > . >
First
|
Prev
|
Pages: 1 2 3 Prev: Macro help needed Next: HELP: Email body transcript deleted when receiving response emails |