#1
|
|||
|
|||
This macros are not working in office english version 2007
I'm pretty new to making macros for Excel in VB.
There was a VBA help for me in one German excel forum. The problem is that created macros are working only in office 2007 German version but when I start this in office 2007 English version I receive always the debug mistake “run time error 13” type mismatch. HTML Code:
strName = wksZiel.Cells(4, 3).Value It looks like there is a mistake in name or format or date (I am not sure). Down bellow there are 3 maps which are not working in office 2007 english version; 1. Master table = here are macros 2. Source file = here are starting and ending time from employee 3. Destination file = in this file I like to transfer starting and ending time from source file When you open master table you got 3 buttons there (1-3). Function of button 1 is to take information from source file. Function of button 2 is to bring information (starting and ending time) to destination map. Function of button 3 is to transfer the starting time between source and destination file. So I’ve put also the comment in macro code to understand it easier. Here is the macro; Code:
Private Sub Daten_uebertragen() Const dblTimeVor As Double = 0.3 '30 Minut Dim strName As String 'column "A" in the source or target sheet in cell "C4" Dim strDepartment As String 'column "A" Dim strStart As String 'Starting time - Column B Dim strEnd As String 'Ending time - Column C Dim datDateStart As Date Dim strTimeStart As String Dim dblTimeStart As Double, dblTimeNew As Double Dim datDateEnd As Date Dim strTimeEnd As String Dim strAMPM As String Dim lngSpalte As Long Dim rngDatum As Range, rngName As Range Dim vAuswahl As Variant Application.ScreenUpdating = False For Each wksZiel In wbZiel.Worksheets Select Case wksZiel.Name Case "Data" 'please not edit Case Else 'Name read in target sheet strName = wksZiel.Cells(4, 3).Value With wksQuelle 'Look up name in column "A". Set rngName = .Columns(1).Find(What:=strName, LookIn:=xlValues, lookat:=xlWhole) If rngName Is Nothing Then vAuswahl = MsgBox("Name " & strName & " not find in source file!", _ Buttons:=vbAbortRetryIgnore) If vAuswahl = vbAbort Then GoTo Beenden Else lngZeile_Q = rngName.Row + 1 Do Until .Cells(lngZeile_Q, 2) = "" 'the next name is column "B" blank 'Read data from row into variables strDepartment = .Cells(lngZeile_Q, 1) strStart = .Cells(lngZeile_Q, 2) strEnd = .Cells(lngZeile_Q, 3) 'Date from Startdate/-Time determine datDateStart = DateSerial(Year:=Mid(strStart, 7, 4), Month:=Mid(strStart, 1, 2), Day:=Mid(strStart, 4, 2)) 'Time from the start date / time to determine as text strTimeStart = Trim(Mid(strStart, InStr(1, strStart, " ") + 1)) 'Date from the end date / time to determine datDateEnd = DateSerial(Year:=Mid(strEnd, 7, 4), Month:=Mid(strEnd, 1, 2), Day:=Mid(strEnd, 4, 2)) 'Time from the end date / time to determine as text strTimeEnd = Trim(Mid(strEnd, InStr(1, strEnd, " ") + 1)) 'AM / PM of start time separating strAMPM = Right(strTimeStart, 2) 'Start date set for the target column If strAMPM = "AM" Then lngSpalte = 5 'column E ElseIf strAMPM = "PM" Then lngSpalte = 7 'column G End If If strAMPM = "AM" And strTimeStart = "12:00 AM" Then Debug.Print Date End If With wksZiel 'Start date in column A of the service plan looking Set rngDatum = .Columns(1).Find(What:=datDateStart, LookIn:=xlValues, lookat:=xlWhole) '*** START TIME from the bottom part of the dblTimeStart = CDbl(VBA.Replace(Format(TimeValue(strTimeStart), "hh:mm"), ":", ",")) 'Start time correction - time recorded is set to Set Time when 30 minutes before set time 'dblTimeStart = strTimeStart Select Case dblTimeStart ' *07:00 AM* Case 0 - dblTimeVor - 0.14 To 0 dblTimeStart = 12 Case 7 - dblTimeVor - 0.14 To 7 '07:00 dblTimeStart = 7 ' *08:00 AM* Case 8 - dblTimeVor - 0.14 To 8 '08:00 dblTimeStart = 8 ' *09:00 AM* Case 9 - dblTimeVor - 0.14 To 9 '09:00 dblTimeStart = 9 ' *10:00 AM* Case 10 - dblTimeVor - 0.14 To 10 '10:00 dblTimeStart = 10 ' *10:45 AM* Case 10.45 - dblTimeVor - 0.14 To 10.45 '10:45 dblTimeStart = 10.45 ' *11:00 AM* Case 11 - dblTimeVor - 0.14 To 11 '11:00 dblTimeStart = 9 ' *11:15 AM* Case 11.15 - dblTimeVor - 0.14 To 11.15 '11:15 dblTimeStart = 11.15 ' *11:45 AM* Case 11.45 - dblTimeVor - 0.14 To 11.45 '11:45 dblTimeStart = 11.45 ' *11:30 AM* Case 11.3 - dblTimeVor - 0.14 To 11.3 '11:30 dblTimeStart = 11# ' *12:00 AM* Case 12 - dblTimeVor - 0.14 To 12 '12:00 dblTimeStart = 12 ' *13:00 PM* Case 13 - dblTimeVor - 0.14 To 13 '13:00 dblTimeStart = 13 ' *14:00 PM* Case 14 - dblTimeVor - 0.14 To 14 '14:00 dblTimeStart = 14 ' *15:00 PM* Case 15 - dblTimeVor - 0.14 To 15 '15:00 dblTimeStart = 15 ' *16:00 PM* Case 16 - dblTimeVor - 0.14 To 16 '16:00 dblTimeStart = 16 ' *16:30 PM* Case 16.3 - dblTimeVor - 0.14 To 16.3 '16:30 dblTimeStart = 16.3 ' *17:00 PM* Case 17 - dblTimeVor - 0.14 To 17 '17:00 dblTimeStart = 17 ' *17:30 PM* Case 17.3 - dblTimeVor - 0.14 To 17.3 '17:30 dblTimeStart = 17.3 ' *17:45 PM* Case 17.45 - dblTimeVor - 0.14 To 17.45 '17:45 dblTimeStart = 17.45 ' *18:00 PM* Case 18 - dblTimeVor - 0.14 To 18 '18:00 dblTimeStart = 18 ' *18:30 PM* Case 18.3 - dblTimeVor - 0.14 To 18.3 '18:30 dblTimeStart = 18.3 End Select '*** END TIME from the bottom part of the '*****Command as the name displayed in window**** dblTimeNew = Application.InputBox(Prompt:="Starting time :" & strStart & vbLf & _ "Ending time: " & strEnd & vbLf _ & vbLf & "(Hours and minutes separated by decimal)", _ Title:="Start time correct - " & strName & " - " & strStart, _ Default:=Format(dblTimeStart, "0.00"), Type:=1) '*****Command as the name displayed in window***** If rngDatum Is Nothing Then MsgBox "Datum " & datDateStart & " im Dienstplan nicht gefunden" Else 'Start and end time separated by commas registered as number lngZeile_Z = rngDatum.Row dblTimeStart = CDbl(VBA.Replace(Format(TimeValue(strTimeStart), "hh:mm"), ":", ",")) 'Start time correction - time recorded is set to target time, _ when up to 30 minutes before set time dblTimeStart = dblTimeNew '''' End If ''''' End Select .Cells(lngZeile_Z, lngSpalte) = dblTimeStart .Cells(lngZeile_Z, lngSpalte + 1) = _ CDbl(VBA.Replace(Format(TimeValue(strTimeEnd), "hh:mm"), ":", ",")) End If End With lngZeile_Q = lngZeile_Q + 1 Loop End If End With End Select Next Beenden: Set wksSteuer = Nothing: Set rngDatum = Nothing Set wbQuelle = Nothing: Set wksQuelle = Nothing Set wbZiel = Nothing: Set wksZiel = Nothing Application.ScreenUpdating = False End Sub For any help or advice I am very thankfull Kind Regards Hans Last edited by macropod; 12-08-2011 at 08:22 PM. Reason: Replaced HTML tags with code tags |
#2
|
||||
|
||||
Hi Hans,
When working with different languages and, hence, regional settings, you need to use the separators applicable to those languages and regions. FWIW, I don't get an error with 'strName = wksZiel.Cells(4, 3).Value'. I note that you hadn't declared some variables, including wksZiel, wbZiel and wksQuelle. That makes the code harder to test. If you use Option Explicit, that will force variable declaration and trap any variable mis-naming errors.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
|||
|
|||
Hi Paul,
Thank you very much for your advice and reply. You are for sure right about regional setting (PC in company has a regional setting USA). That’s way I’m looking for help in engl. excel forum. Well the macros are created at home on Office 2007 German Version. In the company where I'm working we use Office 2007 engl. Version. And as I know the regional setting are locked on my working PC in the company. I note that you hadn't declared some variables, including wksZiel, wbZiel and wksQuelle I am newbie in VBA could you give me please some advice for declaration. This macro which you have tried I become a help from German excel forum. And at the moment is difficult for the german excel friend to find a mistake cause I have left in this forum my question as open but ther is no reply for this mistake. That’s way I thought to post this question in one English excel forum. For any help I am very thankful. Hans |
#4
|
||||
|
||||
Hi Hans,
For the delarations: Dim wbZiel As Workbook Dim wksZiel As Worksheet Dim wbQuelle As Workbook Dim wksQuelle As Worksheet If you're working with the German regional settings, I believe the separator should be ';', not ','. So, instead of 'strName = wksZiel.Cells(4, 3).Value', you'd use 'strName = wksZiel.Cells(4; 3).Value'. You'll have to make the same changes anywhere there's a ',' separator - for example, Cells(lngZeile_Q, 2).
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
|||
|
|||
Hi Paul,
well I will take a look on monday in the company and schell take your advice. I also think that is the problem in regional setting. Have nice weeken hans |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to make Office 2007 support English Phonetics Symbol??? | tinfanide | Office | 1 | 10-05-2011 06:30 PM |
Microsoft Office Shared MUI (English) 2007 -- Installation failed | manjunathgb | Office | 0 | 04-26-2010 03:07 AM |
Office 2007 removal and re-installation of previous version | Buckeyegator | Office | 1 | 03-25-2010 09:15 PM |
Which Version of Office 2007 to Buy? | FauxAsian | Office | 1 | 03-25-2010 09:10 PM |
office 2000 cant accept 2007 macros | vinayak koli | Excel | 0 | 02-11-2009 05:53 AM |