asked on

URGENT VBA(EXCEL) QUESTION! Passing adress and use in formula.. URGENT!

Hello

Are in urgent need to get this function working. I need to base my Master Thesis on the data it will extrace...

Please HELP! a student in URGENT NEED!

I have 2 worksheets. One with tradingdata - containing all the dates the stockexchange has been open.

The other one (No2) when insiders have been buying and selling stocks. (Not everyday compared to the above.. )

Now I would like in the No2. The one with not all the data. To take the date ex. 2001-04-04 and pass to the No1 Worksheet (That works OK!)...

Then No1 WorkSheet should take the value 2001-04-04 and search for the date..

After that it should go down and get NOT the value (which is there but the) "Adress" ex. BA12 or A4 a.s.o
for the date.

How do I extrace the adress from a cell?

Then I want the "adress" to be used in the other worksheet where it should be in the cell to the right of the date and someting like this = (CELL4S ADRESS)...
Like = BA12
From the above mentioned one...

How do I create that formula? How do I extrace the Adress?

I have written some VBA-Code please find it below!!!!

-----------------------
Public Function HamtaVardeSamtSokSkickaSenRange()
Sheets("Wihlborgs").Select
test1 = ActiveCell.Value
Sheets("Data_Wihlborgs").Select

Set NewSearchResult = ActiveSheet.Cells.Find(What:=test1, After:=Range("J4"), LookIn:=xlValues, LookAt:=xlWhole)

Sheets("Wihlborgs").Select
ActiveCell.Offset(0, 1).Activate

Do While Not IsEmpty(ActiveCell)

Sheets("Wihlborgs").Select
ActiveCell.Offset(1, 0).Activate
test1 = ActiveCell.Formula

Sheets("Data_Wihlborgs").Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = test1

Loop

End Function
-----------------------------------

The sheet Data_Wihlborgs is No1 with all Tradingdates
The sheet Wihlborgs is No2 with NOT all Tradingdates

-----------------

Hope ANYONE can help me !!!

PLEASE!!

--------------------------------------------------------
RE:
Thank you!
But that is not really the problem..
Since that is working allright.
The problem is when I find an Insider trading (using search) then I would like to get the adress of that cell ex. A10 returnet to the other sheet and used in an formula like = A10..

So how do I get the adress for the cell?
Create the formula?

THANKS IN ADVANCE!

yairsuari

if i get your meaning what you can do is loop through the cells using a counter and check for every cell if your condition applies (an insider was trading)
this way you will allways know your position

i=1
Do While Not IsEmpty(ActiveCell)
value = Sheets("Wihlborgs").cells(i,1)
i=i+1
loop
if it seems right i can do it in more details

yairsuari

bompa

ASKER

Added Comment in RE: above..
Thanks in advance..

pauloaguia

(Please post comments using the text box below, not editing the question text. otherwise people will loose track of the comment sequence)

You can get the cells address by using the Address property:

straddress = ActiveCell.Address

If you want this in a formula then just do something like

Range("A12").Formula = "=" & strAddress

Hope this helps

Paulo

bompa

ASKER

Hmm Might do..
However I encounter a problem regarding my search function!

Set strAddress = ActiveSheet.Cells.Find(What:=test1, After:=Range("J4"), LookIn:=xlValues, LookAt:=xlWhole)

strAddress = ActiveCell.Address

The problem however then is that the search function DO NOT activate a cell which I would like it to do when it do find the value..

Is there any way to do that?

Thank u!

Steve Knight

If you do:

Set strAddress = ActiveSheet.Cells.Find(What:=test1, After:=Range("J4"), LookIn:=xlValues, LookAt:=xlWhole)

then

strAddress.Address = $X$1 or whatever as a string...

or just
strAddress=ActiveSheet.Cells.Find(What:=test1, After:=Range("J4"), LookIn:=xlValues, LookAt:=xlWhole).address

hth

Steve

Steve Knight

So your function would then be:

Set NewSearchResult = ActiveSheet.Cells.Find(What:=test1, After:=Range("J4"), LookIn:=xlValues, LookAt:=xlWhole)

strAddress=NewSearchResult.address

then you can use strAddress as part of a formula string etc. like Paulo said.

Steve

yairsuari

i was glad to hear from paulo about the adress property (new to this myself) but again if you reach the cells by index you allready know the cells adress (its in your index).
if i understand this fits your second question?

bompa

ASKER

Thank you!

However there are other problems occuring for that..
When running the search..

Range("J4:L8").Select

Set NewSearchResult = Sheets("Data_Wihlborgs").Cells.Find(What:=test1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)

strAddress = NewSearchResult.Address

It should search for the data in J4:L8.. in Sheet Data_Wihlborgs..

Doing so it finds the result the problem is however that that cell is based on a formula. So instead of returning the adress it do return that formula ex. =F$4$
beacause the cell it should take the adress from is based on F4 using the function INDIRECT...
---------------------------
HOWEVER there is also a problem that strAddress which returns =F$4$ should "jump down 1 row...

Can that be done using something like..
NewStrAdress = StrAdress.Offset(0, 1).Activate
Also that cell will return a function using INDIRECT like mentioned above... The adress is pointing to another cell..

THANK YOU VERY MUCH IN ADVANCE...

bompa

ASKER

Sorry for my bad descripton above..
I try to "paint it here"... Ascii... WOW!
A B C
------------------------------------
1 -2000-02-02--2000-03-03--2001-02-30-
2 -0,001111 -- 0,002222 -- 0,003333 -
------------------------------------

Dates ASWELL as returns in above are linked using Indirect to other place ex. F4..
2000-02-02
2000-03-03
2001-02-30

-------------------------------------------------------
Now I want to search for the date using previous mentioned formuls... ex. 2000-02-02 from other sheet..
Then it should find that cell jump down 1 row.
Return the adress to that cell ex A2..
Return that adress so I can go to the other sheet make my function.

THANK YOU IN ADVANCE!!!

Steve Knight

To answer the bit about moving down one row first I think you are confusing a string such as "$a$1" and a range such a range("$a$1").

The Find command will return a range etc. which is why you have to use .address to get the address so instead use:

Set NewSearchResult = Sheets("Data_Wihlborgs").Cells.Find(What:=test1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)

strAddress = NewSearchResult.Offset(0,1).Address

i.e. it is easier to carry out offsets to a range and then get the address than get the address as a string and try to amend that.

hth

Steve

bompa

ASKER

Thank you dragon-it. Now I can move in the right direction. !

But the broblem with the redirection indirect function..
That the cell it do find points at another cell.
So the adress will be the other cell

And end up something like =$A$490 = Not what I want.
The cell I want to adress to is the one that has Indirect function to this one.

The function It uses is something like
=INDIREKT("A"&KOLUMN()-6)

(Sorry for Swedish) Indirekt = Indirect and Kolumn = Column....

However the following problem is still active...

Public Function HamtaVardeSamtSokSkickaSenRange()
Sheets("Wihlborgs").Select
test1 = ActiveCell.Value
Sheets("Data_Wihlborgs").Select
Set strAddress = ActiveSheet.Cells.Find(What:=test1, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole)
strAddressNew = strAddress.Offset(1, 0).Address
Sheets("Wihlborgs").Select
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula = "=" & strAddressNew

Sheets("Wihlborgs").Select
ActiveCell.Offset(1, 0).Activate
Do While Not IsEmpty(ActiveCell)
Sheets("Wihlborgs").Select
ActiveCell.Offset(1, 0).Activate
test1 = ActiveCell.Formula
Sheets("Data_Wihlborgs").Select
ActiveCell.Offset(1, 0).Activate
ActiveCell.Value = test1
Loop
End Function

Steve Knight

Swedish eh, no wonder I didn't recognise the language without all those o's with crosses over etc... or is that Danish? (I've only ever spent two days in your country mind (and a day trip from Copenhagen)!)

Anyway back to the code:

Sorry, I've got somewhat lost in this. Are you saying that test1 is getting set to the formula that is in that cell whereas you want what is shown in that cell. In which case

test1 = ActiveCell.Formula
should be

test1 = ActiveCell.Value to return actual data in cell rather than the formula.

If not, is there any way you can post the sheet on the web perhaps so we could take a look? It sounds a relatively simple problem that perhaps is being solved in a complicated way?

Steve

bompa

ASKER

I think this matter cant be solved due to the fact with the INDIRECT function.
Indirect is used due to the fact that transpose doesn´t work for many cells.
Anyone have a VBA-written transpose who does the transpose job?

bompa

ASKER

HELLO! Everyone! Sorry for the delay..
Wisedom teath had to be taken out THAT HURTS!!

However the code is like this
---------------------------
Public Function DateFunction()
Sheets("Wihlborgs").Select
test1 = ActiveCell.Value
Sheets("WihlborgsTransposed").Activate
Set searchresult = ActiveSheet.Cells.Find(What:=test1, LookIn:=xlValues, LookAt:=xlWhole)
strAddress = searchresult.Address
Sheets("Wihlborgs").Select
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula = "=" & strAddress
ActiveCell.Offset(1, -1).Activate

Do While Not IsEmpty(ActiveCell)
Sheets("Wihlborgs").Select
Rem ActiveCell.Offset(1, 0).Activate
test1 = ActiveCell.Value

Sheets("WihlborgsTransposed").Activate
Set searchresult = ActiveSheet.Cells.Find(What:=test1, LookIn:=xlValues, LookAt:=xlWhole)

strAddress = searchresult.Address
Sheets("Wihlborgs").Select
ActiveCell.Offset(0, 1).Activate
ActiveCell.Formula = "=" & strAddress
ActiveCell.Offset(1, -1).Activate

Loop

End Function
----------------------------------------------------

Now I transposet the data got ride of the INDIRECT function.

It do now return an adress something like this.
=$EA$1
=$FK$1
What is thoes??

I Would like addresses like
=Data_Wihlborgs!Q8

That gives the DataSheet and Adress and do work..

How can I do that..

Thank you all!
And F.Y.I IT HURTS TO TAKE out 2 wisedom teeths.

Thank you
Good Night

pauloaguia

Then you'll have to use some arguments to the Address method.
The syntax to the method is:
expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

If you use
strAddress = searchresult.Address(External:=True)
it should do the trick

The $ signs just mean the reference is fixed. If you copy it to other places it will keep the same refrence and not adapt like to the new cell. (Have you ever noticed that when you copy formulas like =B1 to another cell it changes? That's what I'm refering to)

Hope this helps

Paulo

bompa

ASKER

Thank you!
That sure did a big part of the question

However it do return the value something like
=WihlborgsTransposed!$FJ$5
Which is not good beacause I want to pull the thing to the right and left manually to let it "fill out" with values.

Thank you

ASKER CERTIFIED SOLUTION

pauloaguia

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PashasKY

bompa,
No comment has been added lately (149 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Award points to pauloaguia http:#8004975

Please leave any comments here within 7 days.

-- Please DO NOT accept this comment as an answer ! --

Thanks,

PashasKY
EE Cleanup Volunteer