Problem with VBA-Excel-Hyperlink and search for strings

Hello boys and girls!

I am new in VBA and Excel-macros, with only some small programming experience in my past.

So, my project might look very easy for you-and I will be very happy if you have any suggestions.

My project is:

I have to create an excel worksheet "A", that will have the following properties and do these tasks:


0) Cells of A will have values that will be automatically updated, when A is opened, depending on cells of another Workbook "B"

1) From A, access another excel Workbook "B"

2) Go inside a Hyperlink of the Workbook "B", which is another excel Worksheet "C"

3) Inside C, scan a whole column range (let's say A1-A1000) that have certain string values (compare it with some certain strings - let's say Basketball, Football, Volleyball)

4) Count how many results were found, returned True

5) For each one that returned true, check at that row, for a string at another column (let's say colour of ball, green, red, yellow)

6) Count those results and return the percentages (on cells of A) of how many balls were green, red or yellow

7) Also check the date of the moment that A is opened, calculate the difference of days to another cell of C, and return the results on A

Thank you very much for any suggestions,
Cheers

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Attach a file

Hello guys again.

Sorry for the title, I am new here:-) and was a bit desperate :-).

So I would like your ideas on the following:

As I didn't know how to write a code for clicking hyperlink, I recorded a macro and then saw the code.

"Workbooks.Open Filename:="H:\MP\Copy.xls"
"Range("E24:G24").Select

"Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True

The problem I face now is how to just access that. I don't want to open the other file. I just want to have access on this Hyperlink in order to check for some data there.

Regarding my check there I have done the following:

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

For Countball = 1 To 1000 

If StrComp(Volleyball, Range(E, Countball)) = True OR 
StrComp(Football, Range(C, Countball)) = True Then

Totalballs = Totalballs + 1
If StrComp(Green, Range(R, Countball)) = True Then
Greensnum = Greensnum + 1
End If
End If
Next

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

So, as I have said in my first post, I would like to open one excel file A, that will take data from a hyperlink of another excel, make some search about how many balls are Green and so on, and return the data on the excel file A.

I would appreciate any suggestions.

Unfortunately I cannot post the file that has the hyperlink, cause it is for a project I have for a professor.

Thanks a lot

Hi,
You don't have to supply the original workbook, but just an example workbook.

Your explanation is difficult to understand.

Ok guys!

I've had some progress.

I've managed to access the data of workbook B, by opening and closing the file :-)

Private Sub Autoupdate_Click()

Dim Wb1 As Workbook

Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("H:\MP\Report.xls")
Range("E24:G24").Select

Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 

Dim Volleyball As String, Basketball As String

Volleyball = "Volleyball"
Basketball = "Basketball"

'''''''The problem I face now is how to make the check loop I want.
'''''''I mean the access I have already managed it - Copy paste works
'''''''If someone knows an easy way to access without opening and close, would be happy to hear that
'''''''But mostly need help with this loop

Dim Countballs = Integer

For Countballs = 1 To 1500

If StrComp(Volleyball, "Range(E, Countballs)", vbTextCompare) = True Then

Totalballs = Totalballs + 1
If StrComp(Green, "Range(R, Countcsme)",vbTextCompare) = True Then
Greenballs = Greenballs + 1
End If
End If

Next

Percentage = Greenballs / Totalballs %

ThisWorkbook.Sheets(1).Range("A1").Value = Greenballs
ThisWorkbook.Sheets(1).Range("A2").Value = Totalballs
ThisWorkbook.Sheets(1).Range("A3").Value = Percentage

Wb1.Close SaveChanges:=False

Application.ScreenUpdating = True

End Sub

""""Actually the problem is now how to compare the cells I want, (a range of cells) with specific strings, and count the matches with the specific strings to calculate the percentages.

""""I would appreciate any help.
""""By the way, this loop returns me 1500, 1500, 1, so it is always executed :-)
""""Obviously, I am not an experienced programmer :-)
''''''I have problem with this stupid StrComp
"""By the way, the cells I am checking have text, so comparing with strings is the right solution right???
""""Cheers guys!