refference to another workbook cell in format cell(x, y) where x, y are numbers

[antarian]

Hello everyone,

I am prepairing a file which will randomely generate numbers and than based on that choose question etc etc ... Not so important.

But I have different problem, that whenever I work with different workbook I can use all refference possibilities which I am used to...

here is a code which dont work (just a part of that code)



wrong code
Sub question()
'
' question Macro
'
'Workbooks.Open ("tasklist.xlsx")


Workbooks.Open Filename:="tasklist.xlsx", Password:="ms", WriteResPassword:="ms"


x = ActiveWorkbook.Sheets("otazky").Cells(Rows.Count, 1).End(xlUp).Row
Z = ActiveWorkbook.Sheets("otazky").Cells(2, Columns.Count).End(xlToLeft).Column


Set databook = ActiveWorkbook
Set datasheet = databook.Sheets("otazky")


jmeno = "Mira"

occurrence = 2 'this will be entered in a master file but to make it easier I filled in 2
jmenoradek = 1


For Count = 1 To occurrence
jmenoradek = ActiveWorkbook.Sheets("otazky").Range("1:1").Find(what:=jmeno, after:=ActiveWorkbook.Sheets("otazky").Cells(1, jmenoradek), lookat:=xlPart, Searchdirection:=xlNext).Column
Next Count


Randomize


randomnumber = Int((x - 2) * Rnd) + 1
Set afterpokus = datasheet.Cells(1, 16) 'this will be variables, just remove them for this example






vyberradek = datasheet.Range("F1:F50").Find(what:="", after:=afterpokus).Row
MsgBox (vyberradek)
End Sub

correct code
Sub question()
'
' question Macro
'
'Workbooks.Open ("tasklist.xlsx")


Workbooks.Open Filename:="tasklist.xlsx", Password:="ms", WriteResPassword:="ms"


x = ActiveWorkbook.Sheets("otazky").Cells(Rows.Count, 1).End(xlUp).Row
Z = ActiveWorkbook.Sheets("otazky").Cells(2, Columns.Count).End(xlToLeft).Column


Set databook = ActiveWorkbook
Set datasheet = databook.Sheets("otazky")


jmeno = "Mira"

occurrence = 2 'this will be entered in a master file but to make it easier I filled in 2
jmenoradek = 1


For Count = 1 To occurrence
jmenoradek = ActiveWorkbook.Sheets("otazky").Range("1:1").Find(what:=jmeno, after:=ActiveWorkbook.Sheets("otazky").Cells(1, jmenoradek), lookat:=xlPart, Searchdirection:=xlNext).Column
Next Count


Randomize


randomnumber = Int((x - 2) * Rnd) + 1
Set afterpokus = datasheet.Cells(1, "F") 'this will be variables, just remove them for this example to show what is working


vyberradek = datasheet.Range("F1:F50").Find(what:="", after:=afterpokus).Row
MsgBox (vyberradek)
End Sub

the problem is that eventhough afterpokus is same value, it simply did not work when it is entered as a number and I will get error on row
vyberradek = datasheet.Range("F1:F50").Find(what:="", after:=afterpokus).Row

The similar problem is when I want to use reference like this activeworkbook.sheet1
It also dont work and I need to use activeworkbook.sheets("otazky")

simply whenever I work with another workbook, some refference types just dont work in opened workbook and I really dont know why.

I would like to know whats the problem and where is the problem in my code, because I need to use reference in number format, because everything in my code will be variable and its much easier to work with numbers.

 

[antarian]

btw....

sorry I found it as usual it was the smallest thing which I simply did not see, I tried to find it for more than hour and then I found it in few sec.

"F" and 16 is not the same column. (the value on that different columns was same and that was the problem why I did not see that immediately)

sorry for this stupid question and if anyone can delete it ... you can delete that