Help in VBA-Excel needed

[michpapas]

Hello guys 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

Thanks for any suggestions

[michpapas]

Hey guys, sorry for the title, maybe it is too general, and 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

[michpapas]

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!

[michpapas]

Ok guys!!!
I solved the problem!!! :-)
I manage to extract the required info!
Now I have to calculate the difference between todays' date and some cells which have dates.
Anyone knows how to do that?

[Bob Phillips]

Just use code like

numDays = Date - Range("A2").Value