Problem with excel range

[ina]

Good morning all,

I have a Problem with this code, it is stopped in the second row in ms
excel. I Would like to set the column A1 for my query and for each code
I would like to correspondence in colunm B; C; and more

but I have a problem and I do not know where can someone help me

' This macro it is allow me to pass the code from the
Sub Info()

Dim Code, StrFormula1, StrFormula2, StrFormula3 As String
Code = ActiveCell.Value



StrFormula1 = "=CName(""" & Code & """,""CLIENT"")"
varResult = Evaluate(StrFormula1)
StrFormula2 = "=Fees(""ADMIN"", """ & Code & ""","""")"
varResult = Evaluate(StrFormula2)

StrFormula3 = "=CDate(""ROOM"", , """ & Code & """,""CLIENT"", """")"
varResult = Evaluate(StrFormula3)


ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = StrFormula1
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = StrFormula2
ActiveCell.Offset(1, -3).Range("A1").Select
ActiveCell.FormulaR1C1 = StrFormula3

Debug.Print StrFormula1; StrFormula2; StrFormula3



End Sub






Sub getallcode()

' this is my connection
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL Server;SERVER; UID; APP=Microsoft Office
2003;Trusted_Connection=Yes" _
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT Code" & Chr(13) & "" & Chr(10) & "FROM
master.dbo.Hotel" & Chr(13) & "" & Chr(10) & "ORDER BY Code" _
)
.Name = "Query from peakpartners_project"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With


Dim i As Integer
i = 2

Dim ic As String
Range("A" + CStr(i)).Select
ic = Selection
ActiveCell.Offset(0, 1).Range("A1").Select
Dim addr As String
addr = ActiveCell.Address


While ic <> ""

ActiveCell.FormulaR1C1 = ic
Application.Run "Info"
addr = ActiveCell.Address

i = i + 1
Range("A" + CStr(i)).Select
ic = Selection ' I have a problem here
Range(addr).Select

Wend

End Sub

I I would like to have something like this

Code Name DAte
1 Maria 14/04/2006
2 Jenny 14/04/2006
3 Kelly 16/04/2006
4 john 17/03/2006
5 Steve 01/03/2006

Thank you in advance

Ina

 

[Guest]

As near as I can tell, you are trying to do this:

i = 2
do while cells(i+1,1).value <> ""
cells(i,2).Select
Info
i = i + 1
Loop

Also, if you are going to put the formulas themselves in the cells, why do
you need to evaluate them before hand? You never use the results.