# 15 Messages

Digest #3221

3b

Re: What is the best way to get the last closing price? And Alpha? by "Randy Harmelink" rharmelink

# Messages

#### Sat Oct 25, 2014 8:18 am (PDT) . Posted by:

### steven_rourk

something is not working correctly because it is not stopping in the first row where there is data en columnA, its giving me numbers that are past that row.

#### Sat Oct 25, 2014 8:54 am (PDT) . Posted by:

### "Randy Harmelink" rharmelink

If you want it to stop at the first row in column A, you'll need MIN() on

the A row instead of MAX()...

Unfortunately, for the data you gave as an example, only one amout was

greater than 0. So it would be both the MIN() and the MAX() greater than

zero.

On Sat, Oct 25, 2014 at 8:18 AM, steven_rourk@...wrote:

>

> something is not working correctly because it is not stopping in the first

> row where there is data en columnA, its giving me numbers that are past

> that row.

>

the A row instead of MAX()...

Unfortunately, for the data you gave as an example, only one amout was

greater than 0. So it would be both the MIN() and the MAX() greater than

zero.

On Sat, Oct 25, 2014 at 8:18 AM, steven_rourk@

>

> something is not working correctly because it is not stopping in the first

> row where there is data en columnA, its giving me numbers that are past

> that row.

>

#### Sat Oct 25, 2014 10:55 am (PDT) . Posted by:

### steven_rourk

ok, my fault for not giving you the real data.... let me give your the real data, with the real columns:

I

K

M

N

2599

0

0

1144.06

1084.1

2600

0

0

1144.06

1084.1

2601

0

0

1144.06

1084.1

2602

0

0

1144.06

1084.1

2603

0

0

1144.06

1084.1

2604

1144.06

0

0

1084.1

2605

0

0

0

1084.1

2606

0

0

0

1084.1

2607

0

0

#REF!

1084.1

2608

0

1088.68

0

1084.1

2609

0

0

#REF!

1084.1

2610

0

1084.1

0

1087.12

2611

0

0

0

1087.12

2612

0

0

0

1087.12

2613

0

0

0

1087.12

2614

0

0

#REF!

1087.12

2615

0

1087.12

1150.57

#VALUE!

2616

0

0

1150.57

#VALUE!

2617

1150.57

0

0

1063.23

COLUMN I: is data

COLUMN K: is data

COLUMN M: in row 2599 I got the following array entered formula:

=MAX(OFFSET(I2600,0,0,MIN(IF(K2600:K2900>0,ROW(K2600:K2900),99999))-ROW(K2600),1))

I got no problem with the error in column M... I can live with that, because in the rest of the cells its giving me the correct results. For example in M2599 it is correctly giving me 1144.06 that is the maximum value in COLUMN I between the next row 2600 and the first row where there is data in COLUMN K (the yellow selection)

COLUMN N: in row 2599 I got the following array entered formula:

=SMALL(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),COUNTIF(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),0)+1)

In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN K between the next row 2600 and the first row where there is data in COLUMN I (the orange selection).... In this case as there is no data I need it to be 0 or error...But in N2604 the result 1084.1 is correct as this is the minimum value of the blue seccion.

In other words, when there is no data, or there is only ceros, it should give cero or error... and if there is data it should give me the minimum value of that set of data

I

K

M

N

2599

0

0

1144.06

1084.1

2600

0

0

1144.06

1084.1

2601

0

0

1144.06

1084.1

2602

0

0

1144.06

1084.1

2603

0

0

1144.06

1084.1

2604

1144.06

0

0

1084.1

2605

0

0

0

1084.1

2606

0

0

0

1084.1

2607

0

0

#REF!

1084.1

2608

0

1088.68

0

1084.1

2609

0

0

#REF!

1084.1

2610

0

1084.1

0

1087.12

2611

0

0

0

1087.12

2612

0

0

0

1087.12

2613

0

0

0

1087.12

2614

0

0

#REF!

1087.12

2615

0

1087.12

1150.57

#VALUE!

2616

0

0

1150.57

#VALUE!

2617

1150.57

0

0

1063.23

COLUMN I: is data

COLUMN K: is data

COLUMN M: in row 2599 I got the following array entered formula:

=MAX(OFFSET(

I got no problem with the error in column M... I can live with that, because in the rest of the cells its giving me the correct results. For example in M2599 it is correctly giving me 1144.06 that is the maximum value in COLUMN I between the next row 2600 and the first row where there is data in COLUMN K (the yellow selection)

COLUMN N: in row 2599 I got the following array entered formula:

=SMALL(OFFSET(

In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN K between the next row 2600 and the first row where there is data in COLUMN I (the orange selection)..

In other words, when there is no data, or there is only ceros, it should give cero or error... and if there is data it should give me the minimum value of that set of data

#### Sat Oct 25, 2014 6:11 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

Since your formulas exceed the size of the given data, I can't fill it in

for testing.

It would be easier if you would attach a file.

Earlier, I mentioned point & figure. I've uploaded the file I was working

on 7 years ago to the SMF web site:

http://ogres-crypt.com/SMF/Misc/Point-and-Figure-Work-in-Progress.xls

It's doing a trend and reversal type analysis, but based on the number of

boxes and box size, instead of a straight percentage.

On Sat, Oct 25, 2014 at 10:55 AM, steven_rourk@...wrote:

>

> ok, my fault for not giving you the real data.... let me give your the

> real data, with the real columns:

>

>

>

>

> I

>

> K

>

> M

>

> N

>

> 2599

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2600

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2601

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2602

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2603

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2604

>

> 1144.06

>

> 0

>

> 0

>

> 1084.1

>

> 2605

>

> 0

>

> 0

>

> 0

>

> 1084.1

>

> 2606

>

> 0

>

> 0

>

> 0

>

> 1084.1

>

> 2607

>

> 0

>

> 0

>

> #REF!

>

> 1084.1

>

> 2608

>

> 0

>

> 1088.68

>

> 0

>

> 1084.1

>

> 2609

>

> 0

>

> 0

>

> #REF!

>

> 1084.1

>

> 2610

>

> 0

>

> 1084.1

>

> 0

>

> 1087.12

>

> 2611

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2612

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2613

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2614

>

> 0

>

> 0

>

> #REF!

>

> 1087.12

>

> 2615

>

> 0

>

> 1087.12

>

> 1150.57

>

> #VALUE!

>

> 2616

>

> 0

>

> 0

>

> 1150.57

>

> #VALUE!

>

> 2617

>

> 1150.57

>

> 0

>

> 0

>

> 1063.23

>

>

>

> COLUMN I: is data

>

> COLUMN K: is data

>

> COLUMN M: in row 2599 I got the following array entered formula:

>

> =MAX(OFFSET(I2600,0,0,MIN(IF(K2600:K2900>0,ROW(K2600:K2900),99999))-ROW(K2600),1))

>

> I got no problem with the error in column M... I can live with that,

> because in the rest of the cells its giving me the correct results. For

> example in M2599 it is correctly giving me 1144.06 that is the maximum

> value in COLUMN I between the next row 2600 and the first row where there

> is data in COLUMN K (the yellow selection)

>

>

> COLUMN N: in row 2599 I got the following array entered formula:

>

> =SMALL(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),COUNTIF(OFFSET(K2600,0,0,MAX(IF(I2600:I2900>0,ROW(I2600:I2900),0))-ROW(I2600),1),0)+1)

>

> In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN

> K between the next row 2600 and the first row where there is data in COLUMN

> I (the orange selection).... In this case as there is no data I need it to

> be 0 or error...But in N2604 the result 1084.1 is correct as this is the

> minimum value of the blue seccion.

>

> In other words, when there is no data, or there is only ceros, it should

> give cero or error... and if there is data it should give me the minimum

> value of that set of data

>

>

>

>

>

>

>

>

for testing.

It would be easier if you would attach a file.

Earlier, I mentioned point & figure. I've uploaded the file I was working

on 7 years ago to the SMF web site:

http://ogres-crypt.com/SMF/Misc/Point-and-Figure-Work-in-Progress.xls

It's doing a trend and reversal type analysis, but based on the number of

boxes and box size, instead of a straight percentage.

On Sat, Oct 25, 2014 at 10:55 AM, steven_rourk@

>

> ok, my fault for not giving you the real data.... let me give your the

> real data, with the real columns:

>

>

>

>

> I

>

> K

>

> M

>

> N

>

> 2599

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2600

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2601

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2602

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2603

>

> 0

>

> 0

>

> 1144.06

>

> 1084.1

>

> 2604

>

> 1144.06

>

> 0

>

> 0

>

> 1084.1

>

> 2605

>

> 0

>

> 0

>

> 0

>

> 1084.1

>

> 2606

>

> 0

>

> 0

>

> 0

>

> 1084.1

>

> 2607

>

> 0

>

> 0

>

> #REF!

>

> 1084.1

>

> 2608

>

> 0

>

> 1088.68

>

> 0

>

> 1084.1

>

> 2609

>

> 0

>

> 0

>

> #REF!

>

> 1084.1

>

> 2610

>

> 0

>

> 1084.1

>

> 0

>

> 1087.12

>

> 2611

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2612

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2613

>

> 0

>

> 0

>

> 0

>

> 1087.12

>

> 2614

>

> 0

>

> 0

>

> #REF!

>

> 1087.12

>

> 2615

>

> 0

>

> 1087.12

>

> 1150.57

>

> #VALUE!

>

> 2616

>

> 0

>

> 0

>

> 1150.57

>

> #VALUE!

>

> 2617

>

> 1150.57

>

> 0

>

> 0

>

> 1063.23

>

>

>

> COLUMN I: is data

>

> COLUMN K: is data

>

> COLUMN M: in row 2599 I got the following array entered formula:

>

> =MAX(OFFSET(

>

> I got no problem with the error in column M... I can live with that,

> because in the rest of the cells its giving me the correct results. For

> example in M2599 it is correctly giving me 1144.06 that is the maximum

> value in COLUMN I between the next row 2600 and the first row where there

> is data in COLUMN K (the yellow selection)

>

>

> COLUMN N: in row 2599 I got the following array entered formula:

>

> =SMALL(OFFSET(

>

> In N2599 it is giving me incorrectly 1084.1 as the minimum value in COLUMN

> K between the next row 2600 and the first row where there is data in COLUMN

> I (the orange selection)..

> be 0 or error...But in N2604 the result 1084.1 is correct as this is the

> minimum value of the blue seccion.

>

> In other words, when there is no data, or there is only ceros, it should

> give cero or error... and if there is data it should give me the minimum

> value of that set of data

>

>

>

>

>

>

>

>

#### Sat Oct 25, 2014 1:45 pm (PDT) . Posted by:

### bob_15861586

Randy,

It is now working for me. Thank you very much.

Bob

It is now working for me. Thank you very much.

Bob

#### Sat Oct 25, 2014 2:36 pm (PDT) . Posted by:

### blm3@sbcglobal.net

I would typically be running my sheet after market close.

Thanks and regards

Thanks and regards

#### Sat Oct 25, 2014 5:34 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

You should use the RCHGetYahooQuotes() function for closing quotes. It can

get data on up to 200 ticker symbols per invocation.

I don't know of a source for alpha, which could have any number of

definitions.

On Sat, Oct 25, 2014 at 2:36 PM, blm3@... wrote:

>

> I would typically be running my sheet after market close.

>

>

get data on up to 200 ticker symbols per invocation.

I don't know of a source for alpha, which could have any number of

definitions.

On Sat, Oct 25, 2014 at 2:36 PM, blm3@... wrote:

>

> I would typically be running my sheet after market close.

>

>

#### Sat Oct 25, 2014 7:38 pm (PDT) . Posted by:

### tzewei_79

Hi Randy,

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=") where A12 has the ticker works. I'm rying to understand the example at http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html

, but I don't understand what does "h3", "-1" and &addticker=" means.

The syntax shows:

=smfGetTagContent( URL, Tag, Tag#, [Find1], [Find2], [Find3], [Find4], [Convert], [ErrorMsg], [Type] )

Parameter is:

Parameters

URL = Web page to retrieve the HTML tag content from.

Tag = The type of tag to retrieve content from -- "table", "td", "li", "span", etc.

Tag# = The number of tags to skip backward or forward, after function is positioned on the page by "Find1" thru "Find4", before returning data.

Find1 = An optional string value to search for to position the function on the page before skipping backward or forward to find the data to return. Defaults to "<", which should position you at the top of the document.

Find2 = An optional string value to search for to further position the function on the page (after finding the "Find1" string) before skipping backword or forward to find the data to return. Defaults to " ".

Find3 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find2" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Find4 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find3" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Convert = An indicator of whether the data found within the tag should be converted or parsed. Unused at this time. Defaults to 0.

ErrorMsg = An optional value to return if the table cell cannot be found based on specified parameters. Defaults to "Error".

Type = An optional integer value to determine the type of Internet request to make. Defaults to 0. For now, other values are experimental:

0 = XMLHTTP "Get" Request

1 = IE Object Request

2 = HTMLDocument Request

3 = XMLHTTP "Post" Request

What does "h3" tag means? Is this the "header3" html tag? How do we know it is h3 tag from the page? When I inspect the element, I can't relate h3 to CCRC or ticker.

Does -1 means to look backwards after finding the tag?

How did you get the &addticker from the google page? Do you check the source from "inspect element"? They are many folders there and I can only find addticker inside a href.

I'm sorry I always had to ask so many questions before I can't understand the meaning of the syntax examples...

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=") where A12 has the ticker works. I'm rying to understand the example at http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html

, but I don't understand what does "h3"

The syntax shows:

=smfGetTagContent( URL, Tag, Tag#, [Find1], [Find2], [Find3], [Find4], [Convert], [ErrorMsg], [Type] )

Parameter is:

Parameters

URL = Web page to retrieve the HTML tag content from.

Tag = The type of tag to retrieve content from -- "table"

Tag# = The number of tags to skip backward or forward, after function is positioned on the page by "Find1" thru "Find4"

Find1 = An optional string value to search for to position the function on the page before skipping backward or forward to find the data to return. Defaults to "<"

Find2 = An optional string value to search for to further position the function on the page (after finding the "Find1" string) before skipping backword or forward to find the data to return. Defaults to " ".

Find3 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find2" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Find4 = An optional string value to search for to further position the function on the page (after finding the "Find1" thru "Find3" strings) before skipping backward or forward to find the data to return. Defaults to " ".

Convert = An indicator of whether the data found within the tag should be converted or parsed. Unused at this time. Defaults to 0.

ErrorMsg = An optional value to return if the table cell cannot be found based on specified parameters. Defaults to "Error"

Type = An optional integer value to determine the type of Internet request to make. Defaults to 0. For now, other values are experimental:

0 = XMLHTTP "Get" Request

1 = IE Object Request

2 = HTMLDocument Request

3 = XMLHTTP "Post" Request

What does "h3" tag means? Is this the "header3" html tag? How do we know it is h3 tag from the page? When I inspect the element, I can't relate h3 to CCRC or ticker.

Does -1 means to look backwards after finding the tag?

How did you get the &addticker from the google page? Do you check the source from "inspect element"

I'm sorry I always had to ask so many questions before I can't understand the meaning of the syntax examples...

#### Sat Oct 25, 2014 7:58 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

You need to look at the source code of the web page in order to determine

what would be the best combination of search strings. Best if they are

unique on the web page.

In this case, I found this HTML code:

<div class="g-unit g-first"><h3>The Allstate

Corporation </h3>(Public, NYSE:ALL)

<a

href="/finance/portfolio?action=add&addticker=NYSE%3AALL&ei=XmBMVPDmF6mRiAL7q4GACQ"

class=norm>Watch this stock</a>

</div><div class="g-unit norm">Find more results for<b>

The add-in converts all of the HTML encoding (i.e. and &) into

their normal characters. This is the first occurrence of "&addticker=" on

the web page, and it is fairly unique and stable, so I chose to use it.

The tag that I want to extract data from is the "<h3>...</h3>" tag that

contains the company name.

I use the -1 because I want it to find the first "<h3>" BEFORE where it

finds the first "&addticker=" string.

Note there are other possible search strings there:

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Watch

this stock")

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Find

more results")

On Sat, Oct 25, 2014 at 7:38 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> =smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=")

> where A12 has the ticker works. I'm rying to understand the example at

> http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html

>

> , but I don't understand what does "h3", "-1" and &addticker=" means.

>

> What does "h3" tag means? Is this the "header3" html tag? How do we know

> it is h3 tag from the page? When I inspect the element, I can't relate h3

> to CCRC or ticker.

> Does -1 means to look backwards after finding the tag?

> How did you get the &addticker from the google page? Do you check the

> source from "inspect element"? They are many folders there and I can only

> find addticker inside a href.

>

> I'm sorry I always had to ask so many questions before I can't understand

> the meaning of the syntax examples...

>

what would be the best combination of search strings. Best if they are

unique on the web page.

In this case, I found this HTML code:

<div class="g-unit g-first"><h3>The Allstate

Corporation </h3>(Public, NYSE:ALL)

<a

href="/finance/portfolio?action=add&addticker=NYSE%3AALL&ei=XmBMVPDmF6mRiAL7q4GACQ"

class=norm>Watch this stock</a>

</div><div class="g-unit norm">Find more results for<b>

The add-in converts all of the HTML encoding (i.e. and &) into

their normal characters. This is the first occurrence of "&addticker=" on

the web page, and it is fairly unique and stable, so I chose to use it.

The tag that I want to extract data from is the "<h3>...</h3>" tag that

contains the company name.

I use the -1 because I want it to find the first "<h3>" BEFORE where it

finds the first "&addticker=" string.

Note there are other possible search strings there:

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Watch

this stock")

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,">Find

more results")

On Sat, Oct 25, 2014 at 7:38 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> =smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"&addticker=")

> where A12 has the ticker works. I'm rying to understand the example at

> http://ogres-crypt.com/SMF/Documentation/viewer.php?name=smfGetTagContent-Function.html

>

> , but I don't understand what does "h3"

>

> What does "h3" tag means? Is this the "header3" html tag? How do we know

> it is h3 tag from the page? When I inspect the element, I can't relate h3

> to CCRC or ticker.

> Does -1 means to look backwards after finding the tag?

> How did you get the &addticker from the google page? Do you check the

> source from "inspect element"

> find addticker inside a href.

>

> I'm sorry I always had to ask so many questions before I can't understand

> the meaning of the syntax examples...

>

#### Sat Oct 25, 2014 8:25 pm (PDT) . Posted by:

### tzewei_79

Hi Randy,

I don't quite understand the &addticker but I think I understood the other 2 examples you quoted.

It's like I can also use this to extract right?

=smfGetTagContent("https://www.google.com/finance?q="&A12,"h3",-1,"(Public")

It seems that I will need to use any text that has a ">" in front? The others will just return error in the cell?

I don't quite understand the &addticker but I think I understood the other 2 examples you quoted.

It's like I can also use this to extract right?

=smfGetTagContent("https://www.google.com/finance?q="&A12,"

It seems that I will need to use any text that has a ">" in front? The others will just return error in the cell?

#### Sat Oct 25, 2014 9:20 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

You can actually use any text in your search string. I just use the ">"

regularly, because it can make the string more unique. For example, some

people might use "beta" as a search term, as the label in front of a beta

value they want to pull. But if the META tag on the web page uses the word

beta in it, or if beta happens to be in some descriptive text, then "beta"

search string would find the wrong string. But ">Beta", IF APPLICABLE,

might make the string unique.

On Sat, Oct 25, 2014 at 8:25 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I don't quite understand the &addticker but I think I understood the other

> 2 examples you quoted.

>

> It's like I can also use this to extract right?

>

> =smfGetTagContent("https://www.google.com/finance?q=

> "&A12,"h3",-1,"(Public")

>

> It seems that I will need to use any text that has a ">" in front? The

> others will just return error in the cell?

>

>

> __._,_._

>

regularly, because it can make the string more unique. For example, some

people might use "beta" as a search term, as the label in front of a beta

value they want to pull. But if the META tag on the web page uses the word

beta in it, or if beta happens to be in some descriptive text, then "beta"

search string would find the wrong string. But ">Beta", IF APPLICABLE,

might make the string unique.

On Sat, Oct 25, 2014 at 8:25 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I don't quite understand the &addticker but I think I understood the other

> 2 examples you quoted.

>

> It's like I can also use this to extract right?

>

> =smfGetTagContent("https://www.google.com/finance?q=

> "&A12,"

>

> It seems that I will need to use any text that has a ">" in front? The

> others will just return error in the cell?

>

>

> __._,_._

>

#### Sat Oct 25, 2014 8:01 pm (PDT) . Posted by:

### tzewei_79

Hi Randy,

I tried to replace C:\SMF Add-in\RCH_Stock_Market_Functions.xla! with nothing but Excel 2010 says Excel cannot find any data to replace.

How do I add the smfFixLinks function into excel? I opened vb in excel, insert module under Modules folder and added before saving it. The cells still shows #NAME!.

Public Sub smfFixLinks()

ActiveSheet.Cells.Replace _

What:="'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!", _

Replacement:="", _

LookAt:=xlPart, _

SearchOrder:=xlByRows, _

MatchCase:=False, _

SearchFormat:=False, _

ReplaceFormat:=False

End Sub

I tried to replace C:\SMF Add-in\RCH_

How do I add the smfFixLinks function into excel? I opened vb in excel, insert module under Modules folder and added before saving it. The cells still shows #NAME!.

Public Sub smfFixLinks(

ActiveSheet.

What:="

Replacement:

LookAt:=xlPart, _

SearchOrder:

MatchCase:=False, _

SearchFormat:

ReplaceFormat:

End Sub

#### Sat Oct 25, 2014 8:15 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

What you use in the "Find and Replace All" will depend on what prefix is in

your cells. The string I gave in the email was just giving an example of

what it might look like. Yours may not be the same.

An improved version of the smfFixLinks macro is already within the add-in

and available to use. I have it assigned to a toolbar button so I have easy

access to it.

On Sat, Oct 25, 2014 at 8:01 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I tried to replace C:\SMF Add-in\RCH_Stock_Market_Functions.xla! with

> nothing but Excel 2010 says Excel cannot find any data to replace.

>

> How do I add the smfFixLinks function into excel? I opened vb in excel,

> insert module under Modules folder and added before saving it. The cells

> still shows #NAME!.

>

> Public Sub smfFixLinks()

> ActiveSheet.Cells.Replace _

> What:="'C:\SMF Add-in\RCH_Stock_Market_Functions.xla'!", _

> Replacement:="", _

> LookAt:=xlPart, _

> SearchOrder:=xlByRows, _

> MatchCase:=False, _

> SearchFormat:=False, _

> ReplaceFormat:=False

> End Sub

>

>

your cells. The string I gave in the email was just giving an example of

what it might look like. Yours may not be the same.

An improved version of the smfFixLinks macro is already within the add-in

and available to use. I have it assigned to a toolbar button so I have easy

access to it.

On Sat, Oct 25, 2014 at 8:01 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I tried to replace C:\SMF Add-in\RCH_

> nothing but Excel 2010 says Excel cannot find any data to replace.

>

> How do I add the smfFixLinks function into excel? I opened vb in excel,

> insert module under Modules folder and added before saving it. The cells

> still shows #NAME!.

>

> Public Sub smfFixLinks(

> ActiveSheet.

> What:="

> Replacement:

> LookAt:=xlPart, _

> SearchOrder:

> MatchCase:=False, _

> SearchFormat:

> ReplaceFormat:

> End Sub

>

>

#### Sat Oct 25, 2014 8:45 pm (PDT) . Posted by:

### tzewei_79

Hi Randy,

I understood the prefix now.

I have re-installed SMF Add-in. May I know which tab is the toolbar button for the smfFixLinks under? I can't find any special buttons on the menu bar.

I understood the prefix now.

I have re-installed SMF Add-in. May I know which tab is the toolbar button for the smfFixLinks under? I can't find any special buttons on the menu bar.

#### Sat Oct 25, 2014 9:23 pm (PDT) . Posted by:

### "Randy Harmelink" rharmelink

It's a button you would have to add manually.

Which version of the add-in did you install? If you picked up the beta

version from the "Works in Progress" folder, you can find various

SMF-related items on the context menu brought up with a right mouse click

when you're in a workbook. One option there is to "Fix Links", which would

run the macro.

On Sat, Oct 25, 2014 at 8:45 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I understood the prefix now.

>

> I have re-installed SMF Add-in. May I know which tab is the toolbar button

> for the smfFixLinks under? I can't find any special buttons on the menu bar.

>

Which version of the add-in did you install? If you picked up the beta

version from the "Works in Progress" folder, you can find various

SMF-related items on the context menu brought up with a right mouse click

when you're in a workbook. One option there is to "Fix Links", which would

run the macro.

On Sat, Oct 25, 2014 at 8:45 PM, tzewei_79@yahoo.com.sg [smf_addin] <

smf_addin@yahoogroups.com> wrote:

>

> I understood the prefix now.

>

> I have re-installed SMF Add-in. May I know which tab is the toolbar button

> for the smfFixLinks under? I can't find any special buttons on the menu bar.

>

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

## Tidak ada komentar:

## Posting Komentar