Hyperlink to a cell in different wsheet but in same workbook

[emanuele.musa]

Good morning everyone

I am new in VBA and I am I am stuck since a few days while trying to create an hyperlink from the cell of a worksheet to another cell in a different worksheet but within the same workbook.

Unfortunately I am not able to indicate the SubAddress of the destination cell through two variables called i(the row) and DC(the column), while if i specify the destination cell as " '!A2 " for example, the hyperlink works straight away.

The 2 variables i and DC simply scroll the worksheet Countries, and check cell by cell if there were errors while inputing the data.
In case an error was found: the program notify the error in the Worksheet "ERRORS CHECK" and I would like to transform the notification-message into a "link" to the error: people see the error notification, read the problem, click on it and they are straight in front of the cell that need to be corrected.

Here is my (wrong) code:

Please Login or Register  to view this content.

Instead of the cell A2, I would like to reach as destination: the cell wsCountries.Cells(i, DC)

Does anyone knows how to write the syntax for this?

Thank you very much in advance, I hope it was clear!

Greetings

Emanuel

[davesexcel]

Hi,
What is i and k represent?
or
Do you want to go to the cell that is displayed in Cell A2 column DC

[Ron Coderre]

I didn't analyze all of your code, but I did spot this syntax error....

Please Login or Register  to view this content.

Does that help?

[emanuele.musa]

Hi Dave, apologies for not explaining my self well enough. I will try again a bit more in detail.

"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.

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 a worksheet called ERROR-CHECK.
The users of the database will read all the warning found, they will click in each of them, and each time 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.

PS.
the variables K and DC1 are other 2 integer variables for scrolling the worksheet "ERRORS CHECK".

So:
1) wsErrors.Cells(k, DC1) 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: wsCountries.Cells(i, DC) 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 excplicit address, like:

wsErrors.Hyperlinks.Add Selez, "", " ' " & wsCountries.Name & "'!A2"


I need to replace A2 with the dynamic Variables i and DC.


I hope this explaination looks better... Thanks in advance!

Emanuel