Good evening everyone! (:
I am new in VBA and since a few days I am stuck while trying to create an hyperlink from
the cell of a worksheet to another cell in a different worksheet but within the same workbook.
I am working on two
worksheet called COUNTRIES and ERROR CHECK.
"i" and "dc" are two integer variables which scroll all cells in the worksheet COUNTRIES (i all rows and
DC all columns) with two DO-LOOP functions, until the first empty cell: which represent the end of my database.
The 2 variables i and DC simply check cell by cell, if there were errors while inputing the data.
So: if the
program finds a mistake in the cell H14: the variables will be equal to: i=8 and dc = 14.
If the mistake was in A1, the variables will be equal to: i=1 and dc=1.
Everytime the program finds mistakes in
the database (for example a NON Valid Date), then: a WARNING for each specific error is written in the worksheet ERROR
The users of the database will read all the warning found: they can click in each WARNINGl, so they will be automatically
redirected to the Database (worksheet COUNTRIES), in the exact cell where the error was found... so they can correct the
error straight away.
Here my wrong code:
Set wsCountries = .Worksheets("Countries")
Set wsErrors = .Worksheets("ERRORS CHECK")
Set Selez = wsErrors.Cells(k, j)
wsErrors.Hyperlinks.Add Selez, " #' " & wsCountries.Name & " ' ", subaddress= "cell(i,
is the cell with the WARNING in which I want to add the Hyperlink....
2) When people click on the hyperlink: the
destination will be the worksheet wsCountries and the cell will be the one in which the input-error was found:
where i and dc will have the value of the row and the column where the error was found.
The number of hyperlinks
created, is proportional to the number of errors found by the program while "checking" the database COUNTRIES.
Concluding: instead of using an explicit address, like...
wsErrors.Hyperlinks.Add Selez, "", " ' " & wsCountries.Name & "'!A2"
...I need to replace A2 with the dynamic Variables i and dc.
Does anyone knows how to write the syntax for
Thank you very much in advance, I hope it was clear!