PDA

View Full Version : Only once error 91



does84
01-14-2013, 01:33 AM
Hey there,

I'm a newbie when it comes to VBA so I hope you can help me.
I'm developing a form for a school.
In the combobox you can choose the name of a child. (then I want the form to autofill already inserted data)
When the form autofills in the data I get "error 91 (Object Variable or With Block not set)"
The strange thing is that when you look at the error and you stop the macro/ form, the error doesn't occur anymore and the form works fine.

I work with Excel 2007
Here's the code: (sorry for the Dutch in between)

Private Sub NaamKind_Change()
Dim oRNG As Range
Dim oRNG2 As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("invullen groep 6")
Set oRNG = ws.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
Set ws2 = Worksheets("voorblad")
Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)

'Naam invullen van kind
LrTlE5.Value = KolomB(NaamKind.Value)

With Gr6Inv
'naam leerkracht invullen
LeerkrGr6.Value = oRNG2.Offset(0, 38).Value
'terughalen tabblad cito e5
LrTlE5.Value = oRNG.Offset(0, 1).Value
DleTlE5.Value = oRNG.Offset(0, 2).Value
NivTlE5.Value = oRNG.Offset(0, 3).Value
IlTlE5.Value = oRNG.Offset(0, 4).Value
OaTlE5.Value = oRNG.Offset(0, 5).Value
LrBlE5.Value = oRNG.Offset(0, 6).Value
DleBlE5.Value = oRNG.Offset(0, 7).Value
NivBlE5.Value = oRNG.Offset(0, 8).Value
IlBlE5.Value = oRNG.Offset(0, 9).Value
OaBlE5.Value = oRNG.Offset(0, 10).Value
LrRE5.Value = oRNG.Offset(0, 11).Value
DleRE5.Value = oRNG.Offset(0, 12).Value
NivRE5.Value = oRNG.Offset(0, 13).Value
IlRE5.Value = oRNG.Offset(0, 14).Value
OaRE5.Value = oRNG.Offset(0, 15).Value
LrSE5.Value = oRNG.Offset(0, 16).Value
DleSE5.Value = oRNG.Offset(0, 17).Value
NivSE5.Value = oRNG.Offset(0, 18).Value
IlSE5.Value = oRNG.Offset(0, 19).Value
OaSE5.Value = oRNG.Offset(0, 20).Value

'invullen tabblad november
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 21).Value = "x" Then AfINov6 = True Else: AfINov6 = False
If oRNG.Offset(0, 22).Value = "x" Then GevINov6 = True Else: GevINov6 = False
If oRNG.Offset(0, 23).Value = "x" Then OnINov6 = True Else: OnINov6 = False

OaINov6.Value = oRNG.Offset(0, 24).Value

'kijk tabbladen invullen
KOVZIpKNov6.Value = oRNG.Offset(0, 25).Value
GCMLIpKNov6.Value = oRNG.Offset(0, 26).Value
GCMGIpKNov6.Value = oRNG.Offset(0, 27).Value
HRMDAIpKNov6.Value = oRNG.Offset(0, 28).Value
KOMCIpKNov6.Value = oRNG.Offset(0, 29).Value
OaIepKNov6.Value = oRNG.Offset(0, 30).Value

VVEBIpKNov6.Value = oRNG.Offset(0, 31).Value
NEOIpKNov6.Value = oRNG.Offset(0, 32).Value
ZelfVIpKNov6.Value = oRNG.Offset(0, 33).Value
ZelfstIpKNov6.Value = oRNG.Offset(0, 34).Value
TWHIpKNov6.Value = oRNG.Offset(0, 35).Value
TSIpKNov6.Value = oRNG.Offset(0, 36).Value
OaIapKNov6.Value = oRNG.Offset(0, 37).Value

VZBBDGMbKNov6.Value = oRNG.Offset(0, 38).Value
KOMAMbKNov6.Value = oRNG.Offset(0, 39).Value
BAMRMbKNov6.Value = oRNG.Offset(0, 40).Value
OaMbKNov6.Value = oRNG.Offset(0, 41).Value

'invullen tabblad februari
LrTlFeb6.Value = oRNG.Offset(0, 42).Value
DleTlFeb6.Value = oRNG.Offset(0, 43).Value
NivTlFeb6.Value = oRNG.Offset(0, 44).Value
IlTlFeb6.Value = oRNG.Offset(0, 45).Value
OaTlFeb6.Value = oRNG.Offset(0, 46).Value
LrBlFeb6.Value = oRNG.Offset(0, 47).Value
DleBlFeb6.Value = oRNG.Offset(0, 48).Value
NivBlFeb6.Value = oRNG.Offset(0, 49).Value
IlBlFeb6.Value = oRNG.Offset(0, 50).Value
OaBlFeb6.Value = oRNG.Offset(0, 51).Value
LrRFeb6.Value = oRNG.Offset(0, 52).Value
DleRFeb6.Value = oRNG.Offset(0, 53).Value
NivRFeb6.Value = oRNG.Offset(0, 54).Value
IlRFeb6.Value = oRNG.Offset(0, 55).Value
OaRFeb6.Value = oRNG.Offset(0, 56).Value
LrSFeb6.Value = oRNG.Offset(0, 57).Value
DleSFeb6.Value = oRNG.Offset(0, 58).Value
NivSFeb6.Value = oRNG.Offset(0, 59).Value
IlSFeb6.Value = oRNG.Offset(0, 60).Value
OaSFeb6.Value = oRNG.Offset(0, 61).Value

'invullen tabblad maart
If oRNG.Offset(0, 62).Value = "x" Then SmtTlMrt6 = "Zwak"

If oRNG.Offset(0, 63).Value = "x" Then SmtTlMrt6 = "Matig"

If oRNG.Offset(0, 64).Value = "x" Then SmtTlMrt6 = "Voldoende"

If oRNG.Offset(0, 65).Value = "x" Then SmtTlMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 66).Value = "x" Then SmtTlMrt6 = "Goed"

OaTlMrt6.Value = oRNG.Offset(0, 67).Value

If oRNG.Offset(0, 68).Value = "x" Then SmtBlMrt6 = "Zwak"

If oRNG.Offset(0, 69).Value = "x" Then SmtBlMrt6 = "Matig"

If oRNG.Offset(0, 70).Value = "x" Then SmtBlMrt6 = "Voldoende"

If oRNG.Offset(0, 71).Value = "x" Then SmtBlMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 72).Value = "x" Then SmtBlMrt6 = "Goed"

OaBlMrt6.Value = oRNG.Offset(0, 73).Value

If oRNG.Offset(0, 74).Value = "x" Then SmtRMrt6 = "Zwak"

If oRNG.Offset(0, 75).Value = "x" Then SmtRMrt6 = "Matig"

If oRNG.Offset(0, 76).Value = "x" Then SmtRMrt6 = "Voldoende"

If oRNG.Offset(0, 77).Value = "x" Then SmtRMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 78).Value = "x" Then SmtRMrt6 = "Goed"

OaRMrt6.Value = oRNG.Offset(0, 79).Value

If oRNG.Offset(0, 80).Value = "x" Then SmtSMrt6 = "Zwak"

If oRNG.Offset(0, 81).Value = "x" Then SmtSMrt6 = "Matig"

If oRNG.Offset(0, 82).Value = "x" Then SmtSMrt6 = "Voldoende"

If oRNG.Offset(0, 83).Value = "x" Then SmtSMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 84).Value = "x" Then SmtSMrt6 = "Goed"

OaSMrt6.Value = oRNG.Offset(0, 85).Value

'invullen tabblad mei
'invullen tabblad entreetoets gr 5
TlEn5Mei6.Value = oRNG.Offset(0, 86).Value
OaTlEn5Mei6.Value = oRNG.Offset(0, 87).Value
BlEn5Mei6.Value = oRNG.Offset(0, 88).Value
OaBlEn5Mei6.Value = oRNG.Offset(0, 89).Value
REn5Mei6.Value = oRNG.Offset(0, 90).Value
OaREn5Mei6.Value = oRNG.Offset(0, 91).Value
SEn5Mei6.Value = oRNG.Offset(0, 92).Value
OaSEn5Mei6.Value = oRNG.Offset(0, 93).Value
If oRNG.Offset(0, 94).Value = "x" Then TEn5Mei6 = "V"
If oRNG.Offset(0, 95).Value = "x" Then TEn5Mei6 = "IV"
If oRNG.Offset(0, 96).Value = "x" Then TEn5Mei6 = "III"
If oRNG.Offset(0, 97).Value = "x" Then TEn5Mei6 = "II"
If oRNG.Offset(0, 98).Value = "x" Then TEn5Mei6 = "I"


'invullen tabblad entreetoets gr 6
TlEn6Mei6.Value = oRNG.Offset(0, 99).Value
OaTlEn6Mei6.Value = oRNG.Offset(0, 100).Value
BlEn6Mei6.Value = oRNG.Offset(0, 101).Value
OaBlEn6Mei6.Value = oRNG.Offset(0, 102).Value
REn6Mei6.Value = oRNG.Offset(0, 103).Value
OaREn6Mei6.Value = oRNG.Offset(0, 104).Value
SEn6Mei6.Value = oRNG.Offset(0, 105).Value
OaSEn6Mei6.Value = oRNG.Offset(0, 106).Value
If oRNG.Offset(0, 107).Value = "x" Then TEn6Mei6 = "V"
If oRNG.Offset(0, 108).Value = "x" Then TEn6Mei6 = "IV"
If oRNG.Offset(0, 109).Value = "x" Then TEn6Mei6 = "III"
If oRNG.Offset(0, 110).Value = "x" Then TEn6Mei6 = "II"
If oRNG.Offset(0, 111).Value = "x" Then TEn6Mei6 = "I"

'invullen tabblad instructie
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 112).Value = "x" Then AfIMei6 = True Else: AfIMei6 = False

If oRNG.Offset(0, 113).Value = "x" Then GevIMei6 = True Else: GevIMei6 = False

If oRNG.Offset(0, 114).Value = "x" Then OnIMei6 = True Else: OnIMei6 = False

OaIMei6.Value = oRNG.Offset(0, 115).Value

'invullen tabblad kijk
KOVZIpKMei6.Value = oRNG.Offset(0, 116).Value
GCMLIpKMei6.Value = oRNG.Offset(0, 117).Value
GCMGIpKMei6.Value = oRNG.Offset(0, 118).Value
HRMDAIpKMei6.Value = oRNG.Offset(0, 119).Value
KOMCIpKMei6.Value = oRNG.Offset(0, 120).Value
OaIepKMei6.Value = oRNG.Offset(0, 121).Value

VVEBIpKMei6.Value = oRNG.Offset(0, 122).Value
NEOIpKMei6.Value = oRNG.Offset(0, 123).Value
ZelfvIpKMei6.Value = oRNG.Offset(0, 124).Value
ZelfstIpKMei6.Value = oRNG.Offset(0, 125).Value
TWHIpKMei6.Value = oRNG.Offset(0, 126).Value
TSIpKMei6.Value = oRNG.Offset(0, 127).Value
OaIapKMei6.Value = oRNG.Offset(0, 128).Value

VZBBDGMbKMei6.Value = oRNG.Offset(0, 129).Value
KOMAMbKMei6.Value = oRNG.Offset(0, 130).Value
BAMRMbKMei6.Value = oRNG.Offset(0, 131).Value
OaMbKMei6.Value = oRNG.Offset(0, 132).Value

End With
End Sub



It's a whole list, but I hope you can help me.

The error occurs at this line:

LrTlE5.Value = oRNG.Offset(0, 1).Value
Thanks for you trouble (and time)

BrianMH
01-14-2013, 01:47 AM
What is Gr6Inv? You are using with but not adding a . to the beginning of any of the objects/variables below? You haven't declared it either.

The with statement saves typing out a variable multiple times as you just. IE


'....code declaring and assigning object to variable mycell

With mycell 'note the . at the start of each line stopping my from having to type out mycell each time.
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

does84
01-14-2013, 02:34 AM
What is Gr6Inv? You are using with but not adding a . to the beginning of any of the objects/variables below? You haven't declared it either.




The with statement saves typing out a variable multiple times as you just. IE



'....code declaring and assigning object to variable mycell


With mycell 'note the . at the start of each line stopping my from having to type out mycell each time.
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With


Gr6Inv is the name of the form.
The form is used to fill in data several times a year.
For example: When you want to fill in the form for the second time in the year, I want the form to autofill the data inserted the first time you filled it in. I hope you understand what I mean....

BrianMH
01-14-2013, 02:53 AM
Can you post a copy of the spreadsheet with any personal data removed?

does84
01-14-2013, 03:13 AM
Unfortunately I can't. When I zip it it exceeds 1 MB. I can make a RAR file of it but this forum doesn't support that.
I hope this works.....

http://dl.dropbox.com/u/89082073/Leerlingvolgtraject%20VO%202012%202013%20compleet.xlsm

I hope you can work it out with all the Dutch in it :)

Thanks again for your time and effort!

BrianMH
01-14-2013, 07:19 AM
Is it doing this as you type a name? If I use the drop down I don't get an error. When you have it set to the change event each time you type a letter it runs the sub. I believe it is not finding anything at the below line so not assigning a range to oRng2. It wouldn't find anything until you have type the whole name.


Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)



I changed the top line of the sub to the below and it worked.


Private Sub NaamKind_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)


Also are you going to be entering new names via this form? If so you might want to add that if the name isn't found on voorblad then to stop running your code.

does84
01-14-2013, 07:45 AM
Is it doing this as you type a name? If I use the drop down I don't get an error. When you have it set to the change event each time you type a letter it runs the sub. I believe it is not finding anything at the below line so not assigning a range to oRng2. It wouldn't find anything until you have type the whole name.


Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)



I changed the top line of the sub to the below and it worked.


Private Sub NaamKind_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)


Also are you going to be entering new names via this form? If so you might want to add that if the name isn't found on voorblad then to stop running your code.

I only use the dropdown menu. I don't type in the name.
I changed the top line as you've said but then I get the same error.

I'll explain how I work with the file. (maybe that helps understand it :dunno )
The purpose of the form is to have information about a child that's going to the next school (from primairy school to college in Britain?).
When using this excel file I fill in the form using the button 'Basisgegevens kinderen invullen' (translation: basicdata children). Here I'll fill in general information about the child (birth date etc.). All this data is stored in 'voorblad'.
When the child is in our sixth group (gr 6). I open the form using the button 'Invullen groep 6' (translation: fill in sixth group). Then using the drop down menu I choose a child. After that (the first time the form remains empty) I full in the tab 'Cito E5' (this is a test the children made) and 'November'.
The tabs in the form 'Gr6Inv' (and 'Gr7Inv' and 'Gr8Inv) are all specific times in the schoolyear where testresults need to be entered in the form. That's why, when you open the form a second or third time, I want the data to be retrieved from the sheet where the data is written.

As I said I (unfortunately) still get the same error when I changed the top line.

BrianMH
01-14-2013, 07:52 AM
And there is definitely something for the name you drop down to on invullen groep 6? When using it as a drop down I get no error at all. The only way I can duplicate it is when typing a name in.

Are you sure the names are exactly the same on the two spreadsheets?

does84
01-14-2013, 08:14 AM
And there is definitely something for the name you drop down to on invullen groep 6? When using it as a drop down I get no error at all. The only way I can duplicate it is when typing a name in.

Are you sure the names are exactly the same on the two spreadsheets?

Have you tried using the form as I use it?
As I explained I first use the button 'Basisgegevens kinderen invullen'. The upper left textbox is the name of the child. In the other textboxes (the first 5) you can just fill something in (doesn't really matter).
Then click on the 'Opslaan en sluiten' button (translation: save and close)
After that open the form 'Gr6Inv' using the 'Invullen groep 6' button.
When I choose the name that I previously filled in (using the dropdown box in the upper right corner) . The first time I get the error. When I click 'End' and try again there's nothing wrong and it works fine.

Here's something else I just discovered trying: When I tried what I've described above and close the sheet (leaving Excel open) and opening it again, it works fine (without the error). But when I completely close Excel and opening it again with the file I again get the error.

So when trying, please close Excel and open it again.
Hope it helps! : pray2:

BrianMH
01-14-2013, 09:01 AM
Ok that is one of the wierdest issues I have seen. It only happens once per application opening. As in you can close the workbook and reopen it and it works as long as you don't close excel completely. Not sure what is causing it to be honest.

does84
01-15-2013, 12:38 AM
Ok that is one of the wierdest issues I have seen. It only happens once per application opening. As in you can close the workbook and reopen it and it works as long as you don't close excel completely. Not sure what is causing it to be honest.

So you get the same error as I do?
Don't you have any connections who can find out what's wrong? :help

BrianMH
01-15-2013, 01:28 AM
I've messaged Kenneth Hobs who is way more experienced than I am. Maybe he can shed some light.

Kenneth Hobs
01-15-2013, 07:28 AM
Brian does a fine job answering questions on this forum. I do like flowers though.

Your problem is that your found range returns Nothing. When you enter Debug after an error like that, hover your cursor over parts to see if they resolved to what you expected. Always check after a Find to see if the range returned is Nothing. I did not do that here but you should as a common practice.

I worked up this routine some time back. It returns all the found matches. In this case, we just need the first one. Add it to a Module as shown below.

Then replace your NaamKind_Change() routine in userform Gr6Inv as shown last. Notice that I limited the range to find as well. Change your other find routines as well.

The reason it works the second time is that Find point direction changes after the first Find.

Function FoundRanges(fRange As Range, fStr As String) As Range
Dim objFind As Range
Dim rFound As Range, FirstAddress As String

With fRange
Set objFind = .Find(what:=fStr, After:=fRange.Cells((fRange.Rows.Count), fRange.Columns.Count), _
LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True)
If Not objFind Is Nothing Then
Set rFound = objFind
FirstAddress = objFind.Address
Do
Set objFind = .FindNext(objFind)
If Not objFind Is Nothing Then Set rFound = Union(objFind, rFound)
Loop While Not objFind Is Nothing And objFind.Address <> FirstAddress
End If
End With
Set FoundRanges = rFound
End Function
Private Sub NaamKind_Change()
Dim oRNG As Range
Dim oRNG2 As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim r As Range

Set ws = Worksheets("invullen groep 6")
'Set oRNG = ws.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
Set r = ws.Range("A5", ws.Range("A" & Rows.Count).End(xlUp))
Set oRNG = FoundRanges(r, NaamKind.Value)
'Debug.Print oRNG.Address

Set ws2 = Worksheets("voorblad")
Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)


'Naam invullen van kind

LrTlE5.Value = KolomB(NaamKind.Value)


With Gr6Inv
'naam leerkracht invullen
LeerkrGr6.Value = oRNG2.Offset(0, 38).Value

'terughalen tabblad cito e5

LrTlE5.Value = oRNG.Offset(0, 1).Value
DleTlE5.Value = oRNG.Offset(0, 2).Value
NivTlE5.Value = oRNG.Offset(0, 3).Value
IlTlE5.Value = oRNG.Offset(0, 4).Value
OaTlE5.Value = oRNG.Offset(0, 5).Value
LrBlE5.Value = oRNG.Offset(0, 6).Value
DleBlE5.Value = oRNG.Offset(0, 7).Value
NivBlE5.Value = oRNG.Offset(0, 8).Value
IlBlE5.Value = oRNG.Offset(0, 9).Value
OaBlE5.Value = oRNG.Offset(0, 10).Value
LrRE5.Value = oRNG.Offset(0, 11).Value
DleRE5.Value = oRNG.Offset(0, 12).Value
NivRE5.Value = oRNG.Offset(0, 13).Value
IlRE5.Value = oRNG.Offset(0, 14).Value
OaRE5.Value = oRNG.Offset(0, 15).Value
LrSE5.Value = oRNG.Offset(0, 16).Value
DleSE5.Value = oRNG.Offset(0, 17).Value
NivSE5.Value = oRNG.Offset(0, 18).Value
IlSE5.Value = oRNG.Offset(0, 19).Value
OaSE5.Value = oRNG.Offset(0, 20).Value

'invullen tabblad november
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 21).Value = "x" Then AfINov6 = True Else: AfINov6 = False
If oRNG.Offset(0, 22).Value = "x" Then GevINov6 = True Else: GevINov6 = False
If oRNG.Offset(0, 23).Value = "x" Then OnINov6 = True Else: OnINov6 = False

OaINov6.Value = oRNG.Offset(0, 24).Value

'kijk tabbladen invullen
KOVZIpKNov6.Value = oRNG.Offset(0, 25).Value
GCMLIpKNov6.Value = oRNG.Offset(0, 26).Value
GCMGIpKNov6.Value = oRNG.Offset(0, 27).Value
HRMDAIpKNov6.Value = oRNG.Offset(0, 28).Value
KOMCIpKNov6.Value = oRNG.Offset(0, 29).Value
OaIepKNov6.Value = oRNG.Offset(0, 30).Value

VVEBIpKNov6.Value = oRNG.Offset(0, 31).Value
NEOIpKNov6.Value = oRNG.Offset(0, 32).Value
ZelfVIpKNov6.Value = oRNG.Offset(0, 33).Value
ZelfstIpKNov6.Value = oRNG.Offset(0, 34).Value
TWHIpKNov6.Value = oRNG.Offset(0, 35).Value
TSIpKNov6.Value = oRNG.Offset(0, 36).Value
OaIapKNov6.Value = oRNG.Offset(0, 37).Value

VZBBDGMbKNov6.Value = oRNG.Offset(0, 38).Value
KOMAMbKNov6.Value = oRNG.Offset(0, 39).Value
BAMRMbKNov6.Value = oRNG.Offset(0, 40).Value
OaMbKNov6.Value = oRNG.Offset(0, 41).Value

'invullen tabblad februari
LrTlFeb6.Value = oRNG.Offset(0, 42).Value
DleTlFeb6.Value = oRNG.Offset(0, 43).Value
NivTlFeb6.Value = oRNG.Offset(0, 44).Value
IlTlFeb6.Value = oRNG.Offset(0, 45).Value
OaTlFeb6.Value = oRNG.Offset(0, 46).Value
LrBlFeb6.Value = oRNG.Offset(0, 47).Value
DleBlFeb6.Value = oRNG.Offset(0, 48).Value
NivBlFeb6.Value = oRNG.Offset(0, 49).Value
IlBlFeb6.Value = oRNG.Offset(0, 50).Value
OaBlFeb6.Value = oRNG.Offset(0, 51).Value
LrRFeb6.Value = oRNG.Offset(0, 52).Value
DleRFeb6.Value = oRNG.Offset(0, 53).Value
NivRFeb6.Value = oRNG.Offset(0, 54).Value
IlRFeb6.Value = oRNG.Offset(0, 55).Value
OaRFeb6.Value = oRNG.Offset(0, 56).Value
LrSFeb6.Value = oRNG.Offset(0, 57).Value
DleSFeb6.Value = oRNG.Offset(0, 58).Value
NivSFeb6.Value = oRNG.Offset(0, 59).Value
IlSFeb6.Value = oRNG.Offset(0, 60).Value
OaSFeb6.Value = oRNG.Offset(0, 61).Value

'invullen tabblad maart
If oRNG.Offset(0, 62).Value = "x" Then SmtTlMrt6 = "Zwak"

If oRNG.Offset(0, 63).Value = "x" Then SmtTlMrt6 = "Matig"

If oRNG.Offset(0, 64).Value = "x" Then SmtTlMrt6 = "Voldoende"

If oRNG.Offset(0, 65).Value = "x" Then SmtTlMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 66).Value = "x" Then SmtTlMrt6 = "Goed"

OaTlMrt6.Value = oRNG.Offset(0, 67).Value

If oRNG.Offset(0, 68).Value = "x" Then SmtBlMrt6 = "Zwak"

If oRNG.Offset(0, 69).Value = "x" Then SmtBlMrt6 = "Matig"

If oRNG.Offset(0, 70).Value = "x" Then SmtBlMrt6 = "Voldoende"

If oRNG.Offset(0, 71).Value = "x" Then SmtBlMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 72).Value = "x" Then SmtBlMrt6 = "Goed"

OaBlMrt6.Value = oRNG.Offset(0, 73).Value

If oRNG.Offset(0, 74).Value = "x" Then SmtRMrt6 = "Zwak"

If oRNG.Offset(0, 75).Value = "x" Then SmtRMrt6 = "Matig"

If oRNG.Offset(0, 76).Value = "x" Then SmtRMrt6 = "Voldoende"

If oRNG.Offset(0, 77).Value = "x" Then SmtRMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 78).Value = "x" Then SmtRMrt6 = "Goed"

OaRMrt6.Value = oRNG.Offset(0, 79).Value

If oRNG.Offset(0, 80).Value = "x" Then SmtSMrt6 = "Zwak"

If oRNG.Offset(0, 81).Value = "x" Then SmtSMrt6 = "Matig"

If oRNG.Offset(0, 82).Value = "x" Then SmtSMrt6 = "Voldoende"

If oRNG.Offset(0, 83).Value = "x" Then SmtSMrt6 = "Ruim voldoende"

If oRNG.Offset(0, 84).Value = "x" Then SmtSMrt6 = "Goed"

OaSMrt6.Value = oRNG.Offset(0, 85).Value

'invullen tabblad mei
'invullen tabblad entreetoets gr 5
TlEn5Mei6.Value = oRNG.Offset(0, 86).Value
OaTlEn5Mei6.Value = oRNG.Offset(0, 87).Value
BlEn5Mei6.Value = oRNG.Offset(0, 88).Value
OaBlEn5Mei6.Value = oRNG.Offset(0, 89).Value
REn5Mei6.Value = oRNG.Offset(0, 90).Value
OaREn5Mei6.Value = oRNG.Offset(0, 91).Value
SEn5Mei6.Value = oRNG.Offset(0, 92).Value
OaSEn5Mei6.Value = oRNG.Offset(0, 93).Value
If oRNG.Offset(0, 94).Value = "x" Then TEn5Mei6 = "V"
If oRNG.Offset(0, 95).Value = "x" Then TEn5Mei6 = "IV"
If oRNG.Offset(0, 96).Value = "x" Then TEn5Mei6 = "III"
If oRNG.Offset(0, 97).Value = "x" Then TEn5Mei6 = "II"
If oRNG.Offset(0, 98).Value = "x" Then TEn5Mei6 = "I"


'invullen tabblad entreetoets gr 6
TlEn6Mei6.Value = oRNG.Offset(0, 99).Value
OaTlEn6Mei6.Value = oRNG.Offset(0, 100).Value
BlEn6Mei6.Value = oRNG.Offset(0, 101).Value
OaBlEn6Mei6.Value = oRNG.Offset(0, 102).Value
REn6Mei6.Value = oRNG.Offset(0, 103).Value
OaREn6Mei6.Value = oRNG.Offset(0, 104).Value
SEn6Mei6.Value = oRNG.Offset(0, 105).Value
OaSEn6Mei6.Value = oRNG.Offset(0, 106).Value
If oRNG.Offset(0, 107).Value = "x" Then TEn6Mei6 = "V"
If oRNG.Offset(0, 108).Value = "x" Then TEn6Mei6 = "IV"
If oRNG.Offset(0, 109).Value = "x" Then TEn6Mei6 = "III"
If oRNG.Offset(0, 110).Value = "x" Then TEn6Mei6 = "II"
If oRNG.Offset(0, 111).Value = "x" Then TEn6Mei6 = "I"

'invullen tabblad instructie
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 112).Value = "x" Then AfIMei6 = True Else: AfIMei6 = False

If oRNG.Offset(0, 113).Value = "x" Then GevIMei6 = True Else: GevIMei6 = False

If oRNG.Offset(0, 114).Value = "x" Then OnIMei6 = True Else: OnIMei6 = False

OaIMei6.Value = oRNG.Offset(0, 115).Value

'invullen tabblad kijk
KOVZIpKMei6.Value = oRNG.Offset(0, 116).Value
GCMLIpKMei6.Value = oRNG.Offset(0, 117).Value
GCMGIpKMei6.Value = oRNG.Offset(0, 118).Value
HRMDAIpKMei6.Value = oRNG.Offset(0, 119).Value
KOMCIpKMei6.Value = oRNG.Offset(0, 120).Value
OaIepKMei6.Value = oRNG.Offset(0, 121).Value

VVEBIpKMei6.Value = oRNG.Offset(0, 122).Value
NEOIpKMei6.Value = oRNG.Offset(0, 123).Value
ZelfvIpKMei6.Value = oRNG.Offset(0, 124).Value
ZelfstIpKMei6.Value = oRNG.Offset(0, 125).Value
TWHIpKMei6.Value = oRNG.Offset(0, 126).Value
TSIpKMei6.Value = oRNG.Offset(0, 127).Value
OaIapKMei6.Value = oRNG.Offset(0, 128).Value

VZBBDGMbKMei6.Value = oRNG.Offset(0, 129).Value
KOMAMbKMei6.Value = oRNG.Offset(0, 130).Value
BAMRMbKMei6.Value = oRNG.Offset(0, 131).Value
OaMbKMei6.Value = oRNG.Offset(0, 132).Value


End With

End Sub

BrianMH
01-15-2013, 07:57 AM
Ah I see. I knew it was returning nothing the first time but could not for the life of me understand why. Find changing directions makes sense and explains why it works even after the workbook is closed but the application does not.

I love your function for all the found ranges too, very helpful.

Here are some flowers for you.
http://i.telegraph.co.uk/multimedia/archive/01544/FLOWERS_1544290c.jpg

BrianMH
01-15-2013, 08:02 AM
FYI does84. When I was looking at your code I added option explicit to the top of all the modules and forms code. There were some undeclared variables and variable mispellings. You may want to review that to avoid other errors.