This macros are not working in office english version 2007

hans · #1 12-08-2011, 01:42 PM

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

macropod · #2 12-08-2011, 08:22 PM

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.

hans · #3 12-09-2011, 05:01 AM

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

macropod · #4 12-09-2011, 01:59 PM

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).

hans · #5 12-10-2011, 07:03 AM

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