Translating a sheet

[Traderjoe56]

Hi everyone,
This is a fairly lengthy project, and I kinda want to figure out how to solve most of it on my own, except I've run into a bit of a problem.

basically, I have a survey in like 12 different languages, without a key, and I want to take that sheet with all these languages, create a translation key, and translate everything into english.

I'm using a user defined function called "translate()" I pulled online... and it works beautifully, with the exception that sometimes it says, "language not found". Right now I'm trying to find a way to have the code look for the language not found entries, have it copy the value in the original language, put that value in an input box AS the default (so that the user can just cut/copy it into google translate or something). and the user enters an english value. After the translation key's been fixed by the user, he clicks ok, and the code continues with vlookups to build a final translated table.
Here's the raw table below:

Excel Workbook
	A	B	C	D	E	F
1	Country	Language	volunteer?	volunteered before?	Years volunteered	Overall Experience.
2	??	???	??	???	-	???
3	??	???	??	?	-	??
4	??	???	?	?	??	??
5	Panam	Espaol	S	No	-	Excelente
6	Paraguay	Espaol	S	S	1 ao	Buena
7	Pilipinas	tagalog	Opo	Opo	3 taon	maayos
Sheet57

and here is sample translation key for one of the columns:

Excel Workbook
	A	B
32	USA/Las Vegas, NV	USA/Las Vegas, NV
33	Singapore	Singapore
34	Brasil	Brazil
35	Estados Unidos da Amrica/Las Vegas,NV	United States of America / Las Vegas, NV
36	??????	Language Not Found
37	?????????	Language Not Found
38	???????	Bahrain
39	??????	Jordan
40	???	Qatar
Translation Key

 

[Traderjoe56]

I'm also posting the VBA code I have so far. Bear with the inefficiencies and redudancies in it, I'm just learning how to code. (this is actually the 2nd thing I've ever coded).

Sub columntranslator()

'This macro will allow a user to specify a column
'and will automatically translate that column into english
'pieces of code shamelessly pilfered online.

Sheets.add After:=Sheets(1)
Sheets(2).Name = "Translation Key"

Sheets(1).Select
transclm1 = InputBox("Enter the column letter to translate")
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(4, transclm1).Select
transtop = transclm1 & FinalRow
Range(ActiveCell, transtop).Select
Selection.Copy

Sheets("Translation Key").Select
Range("A2").Select
ActiveSheet.Paste
Tcolb = "A" & FinalRow
Range("A2", Tcolb).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("B2"), Unique:=True

Range("A2", Tcolb).Select
Selection.ClearContents
FinalRow1 = Cells(Rows.Count, 2).End(xlUp).Row
Tcol = "B" & FinalRow1
Range(Tcol).Select
Range("B2", ActiveCell).Select
Selection.Cut
Range("A2").Select
ActiveSheet.Paste
[a1].Value = "en"


For i = 2 To FinalRow1
Range("B" & i).Value = translate(Range("A" & i), Range("a1"))
Next i

Do

Range("B2", "B" & FinalRow1).Find("Language not found").Select
ActiveCell.Offset(, -1).Select
inputbox("please translate", "Language Not Found", No clue how to get active cell value here)

Condition = IsError(Range("B2", "B" & FinalRow1).Find("Language not found"))
Loop Until Condition = False

Sheets.add After:=Sheets(2)
Sheets(3).Name = "Translated table"