Simle Then If

P

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
Peltz said:
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
Click to expand...

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
Peltz said:
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.
Click to expand...

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
Peltz said:
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.
Click to expand...


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
Arithos said:
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
Click to expand...



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
Peltz said:
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.
Click to expand...


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
You must log in or register to reply here.

Similar threads

P
  • Question
Macro won't execute
Replies
1
Views
884
Peltz
P
P
  • Question
Why error?
2
Replies
10
Views
505
Andrew Poulsom
Andrew Poulsom

Forum statistics

Threads
1,214,537
Messages
6,120,096
Members
448,944
Latest member
SarahSomethingExcel100

Share this page

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