PDA

View Full Version : Store and modify column letter in a variable



jhd
02-23-2012, 09:55 AM
Hello,

Firstly, sorry for my bad english!

I'm kinda new to VBA, and I wanna write a macro that will skip X columns and Y lines after a certain Cell, and get its value. For example, I find "john" in the A5 Cell, so I want to go to the cell C3 for example to get its value, writing some code like Range(col+2& ":" &lin-2) (...).

The 'lin' variable is easy to manipulate, cause it's a number already. My doubt is about columns

Here is what I alredy have:


Cells.Find(What:="text", After:=Range("A1"), LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Dim lin, col As String
col = ActiveCell.Address
col = Mid(col, InStr(col, "$") + 1, Len(col) - InStrRev(col, "$"))


If I print the value of 'col', ir returns me the correct column of the cell I searched. How do I tell VBA to return, for example, If I search the A column, it gives me the C column (col+2)?

CatDaddy
02-23-2012, 10:15 AM
Activecell.Offset(2,2)

jhd
02-23-2012, 10:54 AM
lol thanks! May I ask one more thing?

How do I pass an argument from one macro to another?

like:


Sub Macro2("receive variable")
(...)
End Sub

Sub Macrio1()
Application.Run "PERSONAL.xls!Macro1("pass argument to macro 2")"
End Sub

I'm supposing similarities with the C language.

CatDaddy
02-23-2012, 10:58 AM
Sub Macro2 (str as String, i as integer)
'...
End Sub

Sub Macro1()
Macro2 mystring, 4
End sub