Solved: Really basic stuff

[yoinkster]

Hey guys,

I am totally new to trying to hack together macros so I'd be grateful if you could spare my a few minutes to impart some knowledge :P

a) How do you address a variable in VBA ? I'm trying to do an automatic find in my macro and it has

Selection.find(What:="4" *etc*
and what I want to tell it to do is find the value of my variable i, I tried to change it to

Selection.find(What:=i *etc*
but it whinged and obviously finding "i" is no use :P

b) How do you concatenate fixed strings and variables ? I'm trying to select a specific range a column and the basic command is

Range("A1").Select
but I want to do something like

Range(Ai).Select
so that in the loop it selects A1, then A2 then A3 etc.

c) Can you address the looping variable within the loop ?
For example if I am looping over j from 1 to 5, can I address the j in the loop and use it as 1,2,3,4,5 as the loop repeats ?

I think that's all I need for now, thanks :beerchug:

[mdmackillop]

For the first
Sub Test()
Dim i As Long
i = InputBox("Enter number to find")
Selection.Find(i, LookIn:=xlValues, lookat:=xlWhole).Activate
End Sub


Try to avoid selection and activating unless really necessary. This sets a variable to the found cell, if there is one, and gives an option if no result.

Sub Test2()
Dim i As Long
Dim c As Range
i = InputBox("Enter number to find")
Set c = ActiveSheet.Columns(1).Find(i, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
c.Activate
Else
MsgBox i & " not found"
End If
End Sub


Re part 2

Range("A" & i)

You can use the variable within the loop eg


Sub Test3()
Dim i as Long
For i = 1 To 5
Cells(1, i + 3) = Range("A" & i)
Next
End Sub




BTW, Always use Option Explicit and delare your variables.

[yoinkster]

Thanks for that, was a great help.
My next question though, is there an escape character in VBA ?

say I want to do this ::


ActiveCell.Formula "=IF(ISERR(FIND(" ",G2)),0,FIND(" ",G2))"

it then naturally whinges about the additional " so how do you escape over them so it counts them as actual character ?

Also, when I come off the line, it turns the 6 spaces into 2 but I need it to specifically stay as 6 spaces, is this possible ?

[Bob Phillips]

ActiveCell.Formula "=IF(ISERR(FIND("" "",G2)),0,FIND("" "",G2))"

[yoinkster]

As simple as that, thanks.

I'm sure I'm going to come across another problem soon but thanks a lot for the help so far (:

[yoinkster]

Hey guys, me again :P

How do you tell a macro to go down a cell from the currently selected one?

And whats the best way to test for the end of a list, I want to say something roughly like

if cell == empty then end else goto doSomeWork:

[Bob Phillips]

Activecell.Offset(1,0).Select




Do While ActiveCell.Value <> ""
...
Loop