Hi Jim,
I'm glad my code worked out!
By "selecting cells" I'm actually referring to the use of the .Select method
that you use. Referring to cells using A1-type notation is fine, especially
if you have a specific cell or range to refer to. There are several ways of
referring to cells in VBA, and they each have their place.. but focusing on
my point.. it really has to do with the difference between how you would
work with a spreadsheet manually and what a VBA macro needs to do.
When you use RANGE.Select (however you specify the range), you move the
selection pointer in the spreadsheet, highlighting a different cell. Unless
you need to do this so that when the macro ends, the user finds different
cells selected, or for a few rare circumstance, like printing a range of
cells to a printer when printing the selection is convenient, there are
quicker ways to work with cells from a macro.
Instead of changing the Selection in your code, it is usually better to use
a Range variable to keep track of the cells you want to work with/refer to.
For example -
Dim Cell As Range
With ActiveSheet
For Each Cell In
Application.Intersect(ActiveSheet.UsedRange,ActiveSheet.Range("A:A"))
Cell.Value=Cell.Value*2
Next Cell
End With
Will loop through all used cells in column A of the active worksheet and
multiply their value times 2 without changing the selection. (It will give
an error though if some cells are non-numeric).
In your case, you could Dim a variable as a Long integer:
Dim RowNum As Long
And increment that number at each turn of the loop. Then use
Cells(RowNum,1) to refer to column A, Cells(RowNum,2) to refer to column B,
etc..
Where possible, I prefer to define an entire range before beginning the loop
and using For Each as In my first example. But I'm flexible.. There are
times when using a simple For/Next loop with Cells() is the most elegant and
simple. I use Do/Loop when the loop is iterated conditionally rather than a
set number of times or through a set range, much like you have.
Tools for defining a range when you don't already know its bounds include
WORKSHEET.UsedRange, RANGE.CurrentRegion, RANGE.SpecialCells,
Application.Intersect, and Application.Union.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of tom rowe
Sent: Tuesday, November 22, 2011 8:17 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Incrementing Row by 1 in a Variable
Asa, first off, thank you for your fast response.
I think I understand your logic and it seems so simple :-)
In you reference to my rookie mistake and selecting cells, are you
suggesting that I do not need to call out the absolute cell reference (i.e.,
A1) but can use Cells(x.x).Select command?
Thanks again,
Tom
On Tue, Nov 22, 2011 at 8:01 PM, Asa Rossoff <a...@lovetour.info> wrote:
Hi TomR,
You can use the same technique you use to increment the row selected at the
end of the loop to reference cells in the selected row: the Offset property.
(The main rookie mistake I will mention is that there is no need to select
cells at all in a macro - you can reference them anyway... no biggie, but it
is much faster execution).
Changing your problem lines of code to the following should work:
Ext_Number = Selection.Value
Display_Name = Selection.Offset(0,2).Value
MAC_Address = Selection.Offset(0,1).Value
Asa
-----Original Message-----
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of TomR
Sent: Tuesday, November 22, 2011 5:16 PM
To: MS EXCEL AND VBA MACROS
Subject: $$Excel-Macros$$ Incrementing Row by 1 in a Variable
Hi all, I'm new to the group and pretty new to Excel/VBA programming;
learning while drowning.
I'm building a macro to convert a text data/configuration file with
data from an Excel spreadsheet. I'm using a variable that references a
cell where I'm getting the first portion of my data from. I'm using
the variable because the data that is returned the variable I'm
placing in to the config file.
The problem I'm having is I need to increment the row by 1 and do some
comparisons to determine if there is more data, etc. The issue is how
do I increment the row by1?
Here is a snapshot of my code so far. I know there are probably a ton
of "rookie" mistakes or bad practices but I'm under a time crunch and
need to resolve this as soon as possible. I put an arrow in the code
where I'm stuck.
Thanks for any assistance on this.
----------------------------------------------------------------------------
-------------------------
Sub MAC_Address_Routine()
'
' MAC_Address_Routine Macro
'
Dim IP_Address As String ' IP address of SIP Span
Dim Auth_Name As String ' Deafualy UserName of SIP Span
Dim Password As String ' Default Password of SIP Span
Dim Ext_Number As String ' Ext# of SIP telephone
Dim Display_Name As String ' Display name of telephone
Dim MAC_Address As String ' MAC address of telephone and SaveAs file
name
Dim FileName As String ' User to Concatenate file and path
Dim RowCounter As Integer ' Used to increment row number when
looking for Ext #s
Ext_Number = Worksheets("Enter_Users").Range("A9")
' Initialise SIP Span Variables
IP_Address = Worksheets("Enter_Users").Range("C3")
Auth_Name = Worksheets("Enter_Users").Range("C4")
Password = Worksheets("Enter_Users").Range("C5")
RowCounter = 0
' Routine to work through Extension List - This module will look for
Ext #s starting at A9 in MAC_Address
' file and modify the following fields. When the module finds a blank
Ext # cell it is finished.
Range("A9").Select
Do Until Selection.Value = ""
If Selection.Value > "" Then
' Initialise Telephone specific variables
Ext_Number =
Worksheets("Enter_Users").Range("A9") <---------------- Here's my
problem
Display_Name =
Worksheets("Enter_Users").Range("C9 ") <---------------- Here's my
problem
MAC_Address =
Worksheets("Enter_Users").Range("B9") <---------------- Here's my
problem
FileName = ("C:\Users\trowe.VERTICAL\Desktop
\Edge 5000i Script Project\Test Files\" + MAC_Address)
***Data of macro works here
'Move down 1 row
Selection.Offset(1, 0).Select
Else
'Move down 1 row
Selection.Offset(1, 0).Select
End If
If Selection.Value = "" Then Exit Do
Loop
'Define starting point of range
'Range("A9").Select
End Sub
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
Tom
562-896-7879
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
----------------------------------------------------------------------------
--------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (934+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
