Simle Then If

Peltz

Board Regular
Joined
Aug 30, 2011
Messages
87
Hi there, this is driving me nuts.

I Changed location of a couple of files and now suddently I got an error?!

Code:
Sub Slette1()
'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Team As String

Team = "F:\Utkast\Teamarbeid (Jenskladd)\Teamarbeid.xls"
Workbooks.Open Team, UpdateLinks:=True
       If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks("Team").Sheets("Ark3").Cells(25, 1).Text Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
End If
End Sub

Now. The code is launched from Pasientliste.xls. The code also opens Teamarbeid.xls. However, I get the subscript out of range!! Whats happening here? The debugger kicks in at the IF statement...

Thanks
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Hi there, this is driving me nuts.

I Changed location of a couple of files and now suddently I got an error?!

Code:
Sub Slette1()
'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Team As String

Team = "F:\Utkast\Teamarbeid (Jenskladd)\Teamarbeid.xls"
Workbooks.Open Team, UpdateLinks:=True
       If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks("Team").Sheets("Ark3").Cells(25, 1).Text Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
End If
End Sub

Now. The code is launched from Pasientliste.xls. The code also opens Teamarbeid.xls. However, I get the subscript out of range!! Whats happening here? The debugger kicks in at the IF statement...

Thanks

Hei Jens :)

try removing " from the "Team" statement, I think that is your problem

Workbooks("Team") makes you look for a workbook named Team? When its named Teamarbeid.

So you might want to type Workbooks(Team), or Workbooks("Teamarbeid")

Tror det er dette som er problemet ;)

Let me know if you are still having troubles
 
Upvote 0
I've now tried
Team
"Team"
Teamarbeid
"Teamarbeid"
Teamarbeid.xls
"Teamarbeid.xls"

Nothing.

Again. The funny thin is that the only thing I did was moving the files to an open area, and changing the Teamarbeid.xls path, which opens just fine. The Pasientliste.xls is also open.
 
Upvote 0
I've now tried
Team
"Team"
Teamarbeid
"Teamarbeid"
Teamarbeid.xls
"Teamarbeid.xls"

Nothing.

Again. The funny thin is that the only thing I did was moving the files to an open area, and changing the Teamarbeid.xls path, which opens just fine. The Pasientliste.xls is also open.

Have you tested the Immediate window, and see what variables are stored in the: Workbooks("Team").Sheets("Ark3").Cells(25, 1).Text

​And have you tried .value instead?
 
Upvote 0
pffffff, thanks for helping me out here. Takk.

I sendt the file home to my personal computer, and changed the path
Code:
Sub Slette1()
'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Teamarbeid As String




Teamarbeid = "Macintosh HD:Users:JensHetland:Documents:Excel:Teamarbeid.xls"


Workbooks.Open Teamarbeid, UpdateLinks:=True
       If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks(Teamarbeid).Sheets("Ark3").Cells(25, 1).Text Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True


End If


End Sub

... still same error, translated into unintelligible mac language.

Tested in the immediate window as well. Get the error "This workbook contains defined names that conflict with valid cell references "

The cell has the entry: tull


Thanks for your time.
 
Upvote 0
pffffff, thanks for helping me out here. Takk.

I sendt the file home to my personal computer, and changed the path
Code:
Sub Slette1()
'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Teamarbeid As String




Teamarbeid = "Macintosh HD:Users:JensHetland:Documents:Excel:Teamarbeid.xls"


Workbooks.Open Teamarbeid, UpdateLinks:=True
       If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks(Teamarbeid).Sheets("Ark3").Cells(25, 1).Text Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True


End If


End Sub

... still same error, translated into unintelligible mac language.

Tested in the immediate window as well. Get the error "This workbook contains defined names that conflict with valid cell references "

The cell has the entry: tull


Thanks for your time.


I would skip this: (This is the line generating the error yes?)
Code:
[COLOR=#333333]If Workbooks("Pasientliste").Sheets("Behandlingsavdelingen").Cells(7, 1).Text = Workbooks(Teamarbeid).Sheets("Ark3").Cells(25, 1).Text Then[/COLOR]

And rather save the cell Pasientliste cells(7,1) in a stored variable for comparing. Then activate Teamarbeid, and test if cells(25,1) there is infact the same or not. Then continue. and see if that helps.

And again, why .text? and not .value? When comparing I have always used .value I'm only unsure here. I do not know for certain if this might be generating the problem.
 
Upvote 0
Something like this:

Code:
Sub Slette1()'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Teamarbeid As workbook, Pasientliste as workbook


workbooks.open ("Macintosh HD:Users:JensHetland:Documents:Excel:Teamarbeid.xls", UpdateLinks:=True)


set Teamarbeid = workbooks("Teamarbeid.xls")
set Pasientliste = Workbooks("Pasientliste")




Pasientliste.activate


Referanse1 = Sheets("Behandlingsavdelingen").Cells(7, 1).Value


Teamarbeid.activate
       If Referanse1 = Sheets("Ark3").Cells(25, 1).Value Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
End If
 
Upvote 0
Something like this:

Code:
Sub Slette1()'
' Slette1 Makro
' Makro registrert 04.08.2014 av jhetland
'
Dim Teamarbeid As workbook, Pasientliste as workbook


workbooks.open ("Macintosh HD:Users:JensHetland:Documents:Excel:Teamarbeid.xls", UpdateLinks:=True)


set Teamarbeid = workbooks("Teamarbeid.xls")
set Pasientliste = Workbooks("Pasientliste")




Pasientliste.activate


Referanse1 = Sheets("Behandlingsavdelingen").Cells(7, 1).Value


Teamarbeid.activate
       If Referanse1 = Sheets("Ark3").Cells(25, 1).Value Then
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
       Else
        Application.Run "Teamarbeid.xls!Pas1"
        Range("A7:D7,F7:T7").Select
        Range("F7").Activate
        Selection.ClearContents
        Workbooks("Teamarbeid").Close SaveChanges:=True
End If



I tested both .value and .text. No change so I changed it back to .text. I´ll check the code at first chance, and be back. Thank you!


I´ll check and be back.
 
Upvote 0
I tested both .value and .text. No change so I changed it back to .text. I´ll check the code at first chance, and be back. Thank you!


I´ll check and be back.


Hi

At the time Im swamped with work and going on a holiday. I´ll check the code as soon as possible and be back. Again, thank you so much for your help.
 
Upvote 0

Forum statistics

Threads
1,224,315
Messages
6,177,841
Members
452,809
Latest member
mar_luna

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top