VB Code for beginers

[highndry]

Hi

I bought couple of VB book and and also bought programing in excel, but I just can't learn by myself, I hate it. It seems like I learn it from hands on training. Any way, I wanted to know some code for the following example, if you have the time I would appreciate it:

Lets say I have 2 columns and have the following data:
A B
2 3
1 4

How can I write different loop statement to do the following:
Add (Row 1 column A + Row 1 Column B, 2+3) in Row 1 column C, result = 5, and keep doing it until you reach row 1000 using

Do While
Do until
With
For each
For next
or using counter.

Question 2. When I record a macro and then run it, sometimes it fails to run because the cursor was not in a particular cell. What code do I have to write so that no matter where the cursor is the macro will run.

Thanks

 

[Norie]

1 No need for a loop.

LastRow = Range("A65536").End(xlUp).Row

Range("C1:C" & LastRow).Formula = "=A1+B1"

2 Hard to tell, generally when you record a macro the cell references are hard coded.

 

[highndry]

Hi Norie,

That was just an example for me to learn the different ways to loop. Sorry, I did not cearify this in my post.

Thanks

 

[highndry]

Any one wants to help this beginer?????

 

[highndry]

Come on you programmers/ VB gurus' , help out this beginer.

 

[Datsmart]

For a good overview of Excel macros, try this link:

http://office.microsoft.com/trainin...ID=RC011506201033&CTT=6&Origin=RC011506201033

It walks you through what you are look for.

(Edit: Spelling errors)

 

[Norie]

highndry

If you want to learn by 'hands on training' perhaps you could give us something to work with?

This is how you could use a loop to do what you described, but no one who was seriously developing code would use this method.

LastRow = Range("A65536").End(xlUp).Row 

For I = 1 To LastRow
     Range("C" & I).Formula = "=A" & I & "+B" & I
Next I

 

[highndry]

I gues what I'm asking is that lets say I want to perform some calculation in Row 1 of Coulmn E, using data from column A1 through Column D1. I can record this macro, but how do I instruct to do the same calculation in the next row. When I create a loop its just goes to an infinite loop in the same cell.

Here is the Macro I recorded, its an example:

Sub Macro3()
'
' Macro3 Macro
' Macro recorded 11/29/2005 by CONMHI
'

'
ActiveCell.FormulaR1C1 = "=+RC[-4]-RC[-3]*RC[-2]+RC[-1]"
Range("E2").Select
End Sub


How do I tell the macro to perform the same intruction using a loop statements.

 

[Artemus]

Recording a macro will normally only get you close to what you want. Normally it will only hard code a specific location. If you want it to do something repetitive in multiple locations you need to tell it where to go and what to do. One of the simplest loops is the For ... Next loop.

For intRowNum = 1 to 5
Range("C" & intRowNum).Formula = "=A" & intRowNum & "+B" & intRowNum
Next RowNum

In this example intRowNum will represent the row number that you want to act upon and it will start with one and end at five. It will put the formula =A1+B1 in cell C1 and =A2+B2 in cell C2, etc.

 

[HalfAce]

If this were (?) an actual task then something like the example Norie showed you first would be the way to go, specifically because it doesn't loop. (Loops are generally inefficient so other methods should usually be employed if possible.)
That having been said, it's also a good thing to know if you do need to use it, so to answer your original question here are some examples of how it can be done.

Sub Demo1()
Dim Rw As Long
'For - Next example
For Rw = 1 To 1000
    Cells(Rw, 3).Value = Cells(Rw, 1) + Cells(Rw, 2)
Next Rw
End Sub



Sub Demo2()
Dim c As Range
'For Each - Next example
For Each c In Range("C1:C1000")
    c.Value = c.Offset(, -2) + c.Offset(, -1)
Next c
End Sub



Sub Demo3()
Dim Rw As Long
Rw = 1
'While - Wend example
While Rw < 1001
    Cells(Rw, 3).Value = Cells(Rw, 1) + Cells(Rw, 2)
    Rw = Rw + 1
Wend

End Sub



Sub Demo4()
Dim Rw As Long
Rw = 1
'Do While - Loop example
Do While Rw < 1001
    Cells(Rw, 3).Value = Cells(Rw, 1) + Cells(Rw, 2)
    Rw = Rw + 1
Loop
End Sub



Sub Demo5()
Dim Rw As Long
Rw = 1
'Do Until - Loop example
Do Until Rw > 1000
    Cells(Rw, 3).Value = Cells(Rw, 1) + Cells(Rw, 2)
    Rw = Rw + 1
Loop
End Sub



Sub Demo6()
Dim Counter As Integer
Dim Rw As Long
Counter = 0
Rw = 1
'Using a Counter (in a Do - Loop) example
Do
  If Counter < 1000 Then
    Cells(Rw, 3).Value = Cells(Rw, 1) + Cells(Rw, 2)
    Rw = Rw + 1
    Counter = Counter + 1
  Else: Exit Sub
  End If
Loop
End Sub

Hope it helps and happy learning!