?
Solved

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

Posted on 2003-02-19
19
Medium Priority
?
491 Views
Last Modified: 2008-02-01
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!
0
Comment
Question by:bompa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +2
19 Comments
 
LVL 1

Expert Comment

by:yairsuari
ID: 7981254
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
0
 
LVL 1

Expert Comment

by:yairsuari
ID: 7981295
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
0
 

Author Comment

by:bompa
ID: 7981449
Added Comment in RE: above..
Thanks in advance..

0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 9

Expert Comment

by:pauloaguia
ID: 7981814
(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
0
 

Author Comment

by:bompa
ID: 7982192
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!
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 7983129
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
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 7983139
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

0
 
LVL 1

Expert Comment

by:yairsuari
ID: 7983216
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?
0
 

Author Comment

by:bompa
ID: 7983802
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...
0
 

Author Comment

by:bompa
ID: 7983839
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!!!
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 7984052
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
0
 

Author Comment

by:bompa
ID: 7984324
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
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 7986347
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
0
 

Author Comment

by:bompa
ID: 7992379
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?
0
 

Author Comment

by:bompa
ID: 8004928
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

0
 
LVL 9

Expert Comment

by:pauloaguia
ID: 8004975
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
0
 

Author Comment

by:bompa
ID: 8005196
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
0
 
LVL 9

Accepted Solution

by:
pauloaguia earned 700 total points
ID: 8005237
Then just set the RowAbsolute and ColumnAbsolute arguments to False. As in
strAddress = searchresult.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True)

(Meant to say so in the previous post but somehow I just forgot)

Paulo
0
 
LVL 1

Expert Comment

by:PashasKY
ID: 8971404
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
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question