Run-time error '438': Object doesn't support this property or method

**s3a** · 11-13-2012, 12:31 PM

When I run the program, I get a message saying the following.:

Run-time error '438':
Object doesn't support this property or method

When I click "Debug", I get the following line highlighted.:

wsSrc = Workbooks("the_larger_file.xlsm").Sheets(1)

Here is the full code.:

Sub UpdatePeopleHours()
    
    Dim firstRowSrc, lastRowSrc, firstRowDest, lastRowDest, firstColumnDest, lastColumnDest, beforeFrenchTimeInterval, beginningBeforeFrenchTimeInterval, endingBeforeFrenchTimeInterval, columnOfPersonDest, columnOfSAndP, columnOfLT, columnOfProject As Integer
    Dim wsSrc, wsDest1, wsDest2 As Worksheet
    Dim frenchHour, currentPersonSrc As String
    
    wsSrc = Workbooks("the_larger_file.xlsm").Sheets(1)
    wsDest1 = ThisWorkbook.Sheets(1)
    wsDest2 = ThisWorkbook.Sheets(2)
    
    firstRowSrc = wsDest2.Range("B3").Value
    lastRowSrc = wsSrc.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious).row
    
    lastColumnDest = wsDest1.Cell(1, 1).End(xlToLeft).column

    For currentRowSrc = firstRowSrc To lastRowSrc
        
        currentPersonSrc = wsSrc.Cell(currentRowSrc, 11).Value
        currentPersonSkill = wsSrc.Cell(currentRowSrc, 5).Value
        
        columnOfPersonDest = -1
        
        For currentColumnDest = 11 To lastColumnDest 'This for loop finds out which column (the row is constant) the name of a person already added is at in dest ws as well as columns for "S&P", "Loto-Quebec", and "Project"
            
            If wsDest1.Cell(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then
                columnOfPersonDest = currentColumnDest
            End If
            
            If wsDest1.Cell(1, currentColumnDest) = "S&P" Then
                columnOfSAndP = currentColumnDest
            End If
            
            If wsDest1.Cell(1, currentColumnDest) = "Loto Quebec" Then
                columnOfLT = currentColumnDest
            End If
            
            If wsDest1.Cell(1, currentColumnDest) = "Project" Then
                columnOfProject = currentColumnDest
            End If
            
        Next currentColumnDest
        
        If columnOfPersonDest = -1 Then 'In other words, if the person in the src ws was not found in the dest ws
            If wsSrc.Cell(currentRowSrc, 5) = "LT" Then
                wsDest1.Range(wsDest1.Cell(1, columnOfLT + 1), wsDest1.Cell(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cell(1, columnOfLT + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfLT by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfLt+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
                
            Else 'if it's S&P or anything other than LT
                wsDest1.Range(wDest1.Cell(1, columnOfSAndP + 1), wsDest1.Cell(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cell(1, columnOfSAndP + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfSAndP+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
            End If
            
            
        End If
        
        For currentRowDest = firstRowDest To lastRowDest
        
            beforeFrench = 600
            frenchHours = Left(Format(beforeFrenchTimeInterval, "0000"), 2) & ":" & Mid(Format(beforeFrenchTimeInterval, "0000"), 3)
            
            If CDbl(beforeFrench / 100) = Round(CDbl(beforeFrench / 100)) Then
                beforeFrenchTimeInterval = beforeFrenchTimeInterval + 30
            Else
                beforeFrenchTimeInterval = beforeFrenchTimeInterval - 30 + 100
            End If
            
            wsDest1.Cell(currentRowDest, 1) = wsSrc.Cell(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1
            wsDest1.Cell(currentRowDest, 2) = wsSrc.Cell(currentRowSrc, 2) 'write the day in currentRowDest and column B aka 2
            wsDest1.Cell(currentRowDest, 3) = frenchHour 'write the time in currentRowDest and column C aka 3
                        
            If wsSrc.Cell(currentRowSrc, 12).Value <> 0 Or wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 12).Value <> 0 And wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 12).Value > wsSrc.Cell(currentRowSrc, 20).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 20).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 12).Value
                    End If
                    
                End If
            
            End If
            If wsSrc.Cell(currentRowSrc, 13).Value <> 0 Or wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 13).Value <> 0 And wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 13).Value > wsSrc.Cell(currentRowSrc, 21).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 13).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 21).Value
                    End If
                    
                End If
            
            End If
            
            If beforeFrenchTimeInterval >= beginningBeforeFrenchTimeInterval Or beforeFrenchTimeInterval <= endingBeforeFrenchTimeInterval Then
                wsDest1.Cell(currentRowDest, columnOfPersonDest) = "W"
            End If
            
        Next currentRowDest
        
        beforeFrenchTimeInterval = 600
        
    Next currentRowSrc
    
    wsDest2.Range("B3").Value = lastRowSrc + 1 'set the data in sheet 2 of dest file beginning firstRowSrc of next time this macro is run to be 1 more than the current lastRowSrc
    
End Sub

How do I fix whatever is causing this error?

If more information is needed or if I can somehow make it simpler for you guys to help, just tell me what to do.

Any help would be greatly appreciated as always!

**patel45** · 11-13-2012, 12:37 PM

what is the_larger_file.xlsm ? is it an open wb ?
try
set wsSrc = Workbooks("the_larger_file.xlsm").WorkSheets(1)

**s3a** · 11-13-2012, 12:45 PM

I modified that part of my syntax before pasting the code here. Is that what you meant?

Edit: Yes, there are two workbook's open. "This" workbook/file (where the macro is) and the "other"="larger" workbook/file.

**s3a** · 11-13-2012, 01:34 PM

I added the "Set" syntax in front of wsSrc, wsDest1, and wsDest2 and now I get the same error

Run-time error '438':
Object doesn't support this property or method

and when I click "Debug" the following now gets highlighted.:

lastColumnDest = wsDest1.Cell(1, 1).End(xlToLeft).column

Edit: This problem persists whether I put "Set" in front of lastColumnDest or not. Should I not put "Set" in front or should I but I also need to do something else?

**Norie** · 11-13-2012, 01:37 PM

Cell should be Cells.

**s3a** · 11-13-2012, 01:48 PM

Here is the latest macro code.:

Sub UpdatePeopleHours()
    
    Dim firstRowSrc, lastRowSrc, firstRowDest, lastRowDest, firstColumnDest, lastColumnDest, beforeFrenchTimeInterval, beginningBeforeFrenchTimeInterval, endingBeforeFrenchTimeInterval, columnOfPersonDest, columnOfSAndP, columnOfLT, columnOfProject As Integer
    Dim wsSrc, wsDest1, wsDest2 As Worksheet
    Dim frenchHour, currentPersonSrc As String
    
    Set wsSrc = Workbooks("the_larger_file.xlsm").Sheets(1)
    Set wsDest1 = ThisWorkbook.Sheets(1)
    Set wsDest2 = ThisWorkbook.Sheets(2)
    
    firstRowSrc = wsDest2.Range("B3").Value
    lastRowSrc = wsSrc.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious).row
    
    lastColumnDest = wsDest1.Cells(1, 1).End(xlToLeft).column

    For currentRowSrc = firstRowSrc To lastRowSrc
        
        currentPersonSrc = wsSrc.Cells(currentRowSrc, 11).Value
        currentPersonSkill = wsSrc.Cells(currentRowSrc, 5).Value
        
        columnOfPersonDest = -1
        
        For currentColumnDest = 11 To lastColumnDest 'This for loop finds out which column (the row is constant) the name of a person already added is at in dest ws as well as columns for "S&P", "Loto-Quebec", and "Project"
            
            If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then
                columnOfPersonDest = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "S&P" Then
                columnOfSAndP = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Loto Quebec" Then
                columnOfLT = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Project" Then
                columnOfProject = currentColumnDest
            End If
            
        Next currentColumnDest
        
        If columnOfPersonDest = -1 Then 'In other words, if the person in the src ws was not found in the dest ws
            If wsSrc.Cells(currentRowSrc, 5) = "LT" Then
                wsDest1.Range(wsDest1.Cell(1, columnOfLT + 1), wsDest1.Cell(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cell(1, columnOfLT + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfLT by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfLt+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
                
            Else 'if it's S&P or anything other than LT
                wsDest1.Range(wDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
            End If
            
            
        End If
        
        For currentRowDest = firstRowDest To lastRowDest
        
            beforeFrench = 600
            frenchHours = Left(Format(beforeFrenchTimeInterval, "0000"), 2) & ":" & Mid(Format(beforeFrenchTimeInterval, "0000"), 3)
            
            If CDbl(beforeFrench / 100) = Round(CDbl(beforeFrench / 100)) Then
                beforeFrenchTimeInterval = beforeFrenchTimeInterval + 30
            Else
                beforeFrenchTimeInterval = beforeFrenchTimeInterval - 30 + 100
            End If
            
            wsDest1.Cell(currentRowDest, 1) = wsSrc.Cell(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1
            wsDest1.Cell(currentRowDest, 2) = wsSrc.Cell(currentRowSrc, 2) 'write the day in currentRowDest and column B aka 2
            wsDest1.Cell(currentRowDest, 3) = frenchHour 'write the time in currentRowDest and column C aka 3
                        
            If wsSrc.Cell(currentRowSrc, 12).Value <> 0 Or wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 12).Value <> 0 And wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 12).Value > wsSrc.Cell(currentRowSrc, 20).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 20).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 12).Value
                    End If
                    
                End If
            
            End If
            If wsSrc.Cell(currentRowSrc, 13).Value <> 0 Or wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 13).Value <> 0 And wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 13).Value > wsSrc.Cell(currentRowSrc, 21).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 13).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 21).Value
                    End If
                    
                End If
            
            End If
            
            If beforeFrenchTimeInterval >= beginningBeforeFrenchTimeInterval Or beforeFrenchTimeInterval <= endingBeforeFrenchTimeInterval Then
                wsDest1.Cell(currentRowDest, columnOfPersonDest) = "W"
            End If
            
        Next currentRowDest
        
        beforeFrenchTimeInterval = 600
        
    Next currentRowSrc
    
    wsDest2.Range("B3").Value = lastRowSrc + 1 'set the data in sheet 2 of dest file beginning firstRowSrc of next time this macro is run to be 1 more than the current lastRowSrc
    
End Sub

I changed all "Cell" occurrences with "Cells" and now when I try to run the code, I get the following message.:

Run-time error '424':
Object required

and when I click "Debug" the following line is highlighted.:

wsDest1.Range(wDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)

What's currently wrong with the code?

**Norie** · 11-13-2012, 01:51 PM

Without a workbook to run the code on it's hard to tell why you get that error.

Perhaps you could attach a sample workbook?

**s3a** · 11-13-2012, 01:53 PM

Can I send it to you via a private message or something?

**Norie** · 11-13-2012, 01:57 PM

You can attach it here without any sensitive data.

**s3a** · 11-13-2012, 02:28 PM

Okay, I modified them to not have sensitive data.

For "ThisWorkbook_no_sensitive_data.xlsm", the names are just examples as in the file should take the names from "OtherWorkbook_no_sensitive_data.xlsm" from an initially name-less "ThisWorkbook_no_sensitive_data.xlsm".

If you have any questions, ask me and I will answer them.

**Norie** · 11-13-2012, 02:48 PM

You have wDest instead of wsDest.

**s3a** · 11-13-2012, 04:43 PM

Oops. Thanks for catching that.

I've done a few more minor modifications to the macro and here it is.:

Sub UpdatePeopleHours()
    
    Dim firstRowSrc, lastRowSrc, firstRowDest, lastRowDest, firstColumnDest, lastColumnDest, beforeFrenchTimeInterval, beginningBeforeFrenchTimeInterval, endingBeforeFrenchTimeInterval, columnOfPersonDest, columnOfSAndP, columnOfLT, columnOfProject As Integer
    Dim wsSrc, wsDest1, wsDest2 As Worksheet
    Dim frenchHour, currentPersonSrc As String
    
    Set wsSrc = Workbooks("Techs Schedule 1.2 20121108.xlsm").Sheets(1)
    Set wsDest1 = ThisWorkbook.Sheets(1)
    Set wsDest2 = ThisWorkbook.Sheets(2)
    
    firstRowSrc = wsDest2.Range("B3").Value
    lastRowSrc = wsSrc.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious).row
    
    lastColumnDest = wsDest1.Cells(1, 1).End(xlToLeft).column

    For currentRowSrc = firstRowSrc To lastRowSrc
        
        currentPersonSrc = wsSrc.Cells(currentRowSrc, 11).Value
        currentPersonSkill = wsSrc.Cells(currentRowSrc, 5).Value
        
        columnOfPersonDest = -1
        
        For currentColumnDest = 11 To lastColumnDest 'This for loop finds out which column (the row is constant) the name of a person already added is at in dest ws as well as columns for "S&P", "Loto-Quebec", and "Project"
            
            If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then
                columnOfPersonDest = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "S&P" Then
                columnOfSAndP = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Loto Quebec" Then
                columnOfLT = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Project" Then
                columnOfProject = currentColumnDest
            End If
            
        Next currentColumnDest
        
        If columnOfPersonDest = -1 Then 'In other words, if the person in the src ws was not found in the dest ws
            If wsSrc.Cells(currentRowSrc, 5) = "LT" Then
                wsDest1.Range(wsDest1.Cell(1, columnOfLT + 1), wsDest1.Cell(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cell(1, columnOfLT + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfLT by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfLt+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
                
            Else 'if it's S&P or anything other than LT
                wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
            End If
            
            
        End If
        
        For currentRowDest = firstRowDest To lastRowDest
        
            beforeFrench = 600
            frenchHour = Left(Format(beforeFrenchTimeInterval, "0000"), 2) & ":" & Mid(Format(beforeFrenchTimeInterval, "0000"), 3)
            
            If CDbl(beforeFrench / 100) = Round(CDbl(beforeFrench / 100)) Then
                beforeFrenchTimeInterval = beforeFrenchTimeInterval + 30
            Else
                beforeFrenchTimeInterval = beforeFrenchTimeInterval - 30 + 100
            End If
            
            wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1
            wsDest1.Cells(currentRowDest, 2) = wsSrc.Cells(currentRowSrc, 2) 'write the day in currentRowDest and column B aka 2
            wsDest1.Cells(currentRowDest, 3) = frenchHour 'write the time in currentRowDest and column C aka 3
                        
            If wsSrc.Cell(currentRowSrc, 12).Value <> 0 Or wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 12).Value <> 0 And wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 12).Value > wsSrc.Cell(currentRowSrc, 20).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 20).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 12).Value
                    End If
                    
                End If
            
            End If
            If wsSrc.Cell(currentRowSrc, 13).Value <> 0 Or wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 13).Value <> 0 And wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 13).Value > wsSrc.Cell(currentRowSrc, 21).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 13).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 21).Value
                    End If
                    
                End If
            
            End If
            
            If beforeFrenchTimeInterval >= beginningBeforeFrenchTimeInterval Or beforeFrenchTimeInterval <= endingBeforeFrenchTimeInterval Then
                wsDest1.Cell(currentRowDest, columnOfPersonDest) = "W"
            End If
            
        Next currentRowDest
        
        beforeFrenchTimeInterval = 600
        
    Next currentRowSrc
    
    wsDest2.Range("B3").Value = lastRowSrc + 1 'set the data in sheet 2 of dest file beginning firstRowSrc of next time this macro is run to be 1 more than the current lastRowSrc
    
End Sub

When I try to run it, I get a message saying the following.:

Run-time error '1004':
Application-defined or object-defined error

and then when I click "Debug", the following line is highlighted.:

wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1

I don't see any typos here so the issue must be something else. Commenting the line causing the problem makes the next logical line be the problem so that further reinforces my belief that it's not a typo but I'm still not sure.

Edit: I fixed a minor, unrelated issue and, I updated the code in this post (and the problem still exists).

**s3a** · 11-13-2012, 05:51 PM

I found this link ( http://www.mrexcel.com/forum/excel-q...ned-error.html ) which suggested that the name of the file I'm using would be wrong but I re-copied and pasted it just to make sure and it's not the problem.

Is my issue related at all?

**s3a** · 11-13-2012, 06:48 PM

I also tried the following stuff and still failed to fix this most-recent problem.:

wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1)

wsDest1.Cells(currentRowDest, 1).Value = wsSrc.Cells(currentRowSrc, 1)

wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1).Value

wsDest1.Cells(currentRowDest, 1).Value = wsSrc.Cells(currentRowSrc, 1).Value

**bheanloh** · 11-13-2012, 06:54 PM

either one of them is 0. Therefore, you can't refer to cells(0,1).

**s3a** · 11-13-2012, 06:59 PM

Okay so, does that means that my initial syntax was right and that I need to find a problem with the code's logic such that the row parameter is 0?

**s3a** · 11-13-2012, 07:24 PM

I added a firstRowDest value in the second sheet of the destination workbook and now initialize the variable using it as follows.:

firstRowDest = wsDest2.Range("B4").Value

This brought me to another issue (I'm really sorry about this) where the error is:

Run-time error:
Object doesn't support this property or method.

and clicking "Debug" highlights:

If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then

Nothing seems to be 0 to me. So what could be wrong now?

A potentially interesting thing to note is that commenting

If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then
    columnOfPersonDest = currentColumnDest
End If

causes the program to run successfully but hang indefinitely (or so it seems).

If I haven't provided enough information, tell me to and I will.

Edit: Here is the most up-to-date code.:

Sub UpdatePeopleHours()
    
    Dim firstRowSrc, lastRowSrc, firstRowDest, lastRowDest, firstColumnDest, lastColumnDest, beforeFrenchTimeInterval, beginningBeforeFrenchTimeInterval, endingBeforeFrenchTimeInterval, columnOfPersonDest, columnOfSAndP, columnOfLT, columnOfProject As Long
    Dim wsSrc, wsDest1, wsDest2 As Worksheet
    Dim frenchHour, currentPersonSrc As String
    
    Set wsSrc = Workbooks("the_larger_file.xlsm").Sheets(1)
    Set wsDest1 = ThisWorkbook.Sheets(1)
    Set wsDest2 = ThisWorkbook.Sheets(2)
    
    firstRowSrc = wsDest2.Range("B3").Value
    lastRowSrc = wsSrc.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious).row
    
    lastColumnDest = wsDest1.Cells(1, 1).End(xlToLeft).column

    For currentRowSrc = firstRowSrc To lastRowSrc
        
        currentPersonSrc = wsSrc.Cells(currentRowSrc, 11).Value
        currentPersonSkill = wsSrc.Cells(currentRowSrc, 5).Value
        
        columnOfPersonDest = -1
        
        For currentColumnDest = 11 To lastColumnDest 'This for loop finds out which column (the row is constant) the name of a person already added is at in dest ws as well as columns for "S&P", "Loto-Quebec", and "Project"
            
            If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then
                columnOfPersonDest = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "S&P" Then
                columnOfSAndP = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Loto Quebec" Then
                columnOfLT = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Project" Then
                columnOfProject = currentColumnDest
            End If
            
        Next currentColumnDest
        
        If columnOfPersonDest = -1 Then 'In other words, if the person in the src ws was not found in the dest ws
            If wsSrc.Cells(currentRowSrc, 5) = "LT" Then
                wsDest1.Range(wsDest1.Cell(1, columnOfLT + 1), wsDest1.Cell(1, lastColumnDest)).Copy wDest1.Range(wsDest1.Cell(1, columnOfLT + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfLT by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfLt+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
                
            Else 'if it's S&P or anything other than LT
                wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
            End If
            
            
        End If
        
        firstRowDest = wsDest2.Range("B4").Value
        
        For currentRowDest = firstRowDest To lastRowDest
        
            beforeFrench = 600
            frenchHour = Left(Format(beforeFrenchTimeInterval, "0000"), 2) & ":" & Mid(Format(beforeFrenchTimeInterval, "0000"), 3)
            
            If CDbl(beforeFrench / 100) = Round(CDbl(beforeFrench / 100)) Then
                beforeFrenchTimeInterval = beforeFrenchTimeInterval + 30
            Else
                beforeFrenchTimeInterval = beforeFrenchTimeInterval - 30 + 100
            End If
            
            wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1
            wsDest1.Cells(currentRowDest, 2) = wsSrc.Cells(currentRowSrc, 2) 'write the day in currentRowDest and column B aka 2
            wsDest1.Cells(currentRowDest, 3) = frenchHour 'write the time in currentRowDest and column C aka 3
                        
            If wsSrc.Cell(currentRowSrc, 12).Value <> 0 Or wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 12).Value <> 0 And wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 12).Value > wsSrc.Cell(currentRowSrc, 20).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 20).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 12).Value
                    End If
                    
                End If
            
            End If
            If wsSrc.Cell(currentRowSrc, 13).Value <> 0 Or wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 13).Value <> 0 And wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 13).Value > wsSrc.Cell(currentRowSrc, 21).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 13).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 21).Value
                    End If
                    
                End If
            
            End If
            
            If beforeFrenchTimeInterval >= beginningBeforeFrenchTimeInterval Or beforeFrenchTimeInterval <= endingBeforeFrenchTimeInterval Then
                wsDest1.Cell(currentRowDest, columnOfPersonDest) = "W"
            End If
            
        Next currentRowDest
        
        beforeFrenchTimeInterval = 600
        
    Next currentRowSrc
    
    wsDest2.Range("B3").Value = lastRowSrc + 1 'set the data in sheet 2 of dest file beginning firstRowSrc of next time this macro is run to be 1 more than the current lastRowSrc
    
End Sub

**bheanloh** · 11-13-2012, 07:37 PM

If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cell(currentRowSrc, 11) Then

Cell(currentRowSrc, 11) should be Cells(currentRowSrc, 11)

**s3a** · 11-13-2012, 08:12 PM

Thanks again.

After more modifications, I now have the following code which runs without a problem but just hangs indefinitely.:

Sub UpdatePeopleHours()

    Dim firstRowSrc, lastRowSrc, firstRowDest, lastRowDest, firstColumnDest, lastColumnDest, beforeFrenchTimeInterval, beginningBeforeFrenchTimeInterval, endingBeforeFrenchTimeInterval, columnOfPersonDest, columnOfSAndP, columnOfLQ, columnOfProject, currentRowSrc, currentPersonSkill, currentColumnDest, currentRowDest As Long
    Dim wsSrc, wsDest1, wsDest2 As Worksheet
    Dim frenchHour, currentPersonSrc As String

    
    Set wsSrc = Workbooks("the_larger_file.xlsm").Sheets(1)
    Set wsDest1 = ThisWorkbook.Sheets(1)
    Set wsDest2 = ThisWorkbook.Sheets(2)
    
    firstRowSrc = wsDest2.Range("B3").Value
    lastRowSrc = wsSrc.Range("A:A").Find("*", Range("A1"), SearchDirection:=xlPrevious).Row
    
    lastColumnDest = wsDest1.Cells(1, 1).End(xlToLeft).Column

    For currentRowSrc = firstRowSrc To lastRowSrc
        
        currentPersonSrc = wsSrc.Cells(currentRowSrc, 11).Value
        currentPersonSkill = wsSrc.Cells(currentRowSrc, 5).Value
        
        columnOfPersonDest = -1
        
        For currentColumnDest = 11 To lastColumnDest 'This for loop finds out which column (the row is constant) the name of a person already added is at in dest ws as well as columns for "S&P", "Loto-Quebec", and "Project"
            
            If wsDest1.Cells(1, currentColumnDest) = wsSrc.Cells(currentRowSrc, 11) Then
                columnOfPersonDest = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "S&P" Then
                columnOfSAndP = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Loto Quebec" Then
                columnOfLQ = currentColumnDest
            End If
            
            If wsDest1.Cells(1, currentColumnDest) = "Project" Then
                columnOfProject = currentColumnDest
            End If
            
        Next currentColumnDest
        
        If columnOfPersonDest = -1 Then 'In other words, if the person in the src ws was not found in the dest ws
            If wsSrc.Cells(currentRowSrc, 5) = "LQ" Then
                wsDest1.Range(wsDest1.Cell(1, columnOfLQ + 1), wsDest1.Cell(1, lastColumnDest)).Copy wsDest1.Range(wsDest1.Cell(1, columnOfLQ + 2), wsDest1.Cell(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfLQ by 1 column and place the name of the person from wsSrc.Cell(currentRowSrc, 11) into wsDest1.Cell(currentRowDest, columnOfLQ+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
                
            Else 'if it's S&P or anything other than LQ
                wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)
                lastColumnDest = lastColumnDest + 1 'Increment by one since I'm adding a column when shifting and storing new person from previous line
            End If
            
            
        End If
        
        firstRowDest = wsDest2.Range("B4").Value
        
        For currentRowDest = firstRowDest To lastRowDest
        
            beforeFrenchTimeInterval = 600
            frenchHour = Left(Format(beforeFrenchTimeInterval, "0000"), 2) & ":" & Mid(Format(beforeFrenchTimeInterval, "0000"), 3)
            
            If CDbl(beforeFrenchTimeInterval / 100) = Round(CDbl(beforeFrenchTimeInterval / 100)) Then
                beforeFrenchTimeInterval = beforeFrenchTimeInterval + 30
            Else
                beforeFrenchTimeInterval = beforeFrenchTimeInterval - 30 + 100
            End If
            
            wsDest1.Cells(currentRowDest, 1) = wsSrc.Cells(currentRowSrc, 1) 'write the date taken from src: currentRowSrc and column A aka 1
            wsDest1.Cells(currentRowDest, 2) = wsSrc.Cells(currentRowSrc, 2) 'write the day in currentRowDest and column B aka 2
            wsDest1.Cells(currentRowDest, 3) = frenchHour 'write the time in currentRowDest and column C aka 3
                        
            If wsSrc.Cell(currentRowSrc, 12).Value <> 0 Or wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 12).Value <> 0 And wsSrc.Cell(currentRowSrc, 20).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 12).Value > wsSrc.Cell(currentRowSrc, 20).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 20).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 12).Value
                    End If
                    
                End If
            
            End If
            If wsSrc.Cell(currentRowSrc, 13).Value <> 0 Or wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                
                If wsSrc.Cell(currentRowSrc, 13).Value <> 0 And wsSrc.Cell(currentRowSrc, 21).Value <> 0 Then
                    
                    If wsSrc.Cell(currentRowSrc, 13).Value > wsSrc.Cell(currentRowSrc, 21).Value Then
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 13).Value
                    Else
                        beginningBeforeFrenchTimeInterval = wsSrc.Cell(currentRowSrc, 21).Value
                    End If
                    
                End If
            
            End If
            
            If beforeFrenchTimeInterval >= beginningBeforeFrenchTimeInterval Or beforeFrenchTimeInterval <= endingBeforeFrenchTimeInterval Then
                wsDest1.Cell(currentRowDest, columnOfPersonDest) = "W"
            End If
            
        Next currentRowDest
        
        beforeFrenchTimeInterval = 600
        
    Next currentRowSrc
    
    wsDest2.Range("B3").Value = lastRowSrc + 1 'set the data in sheet 2 of dest file beginning firstRowSrc of next time this macro is run to be 1 more than the current lastRowSrc
    
End Sub

Like I said, when I run it, it hangs indefinitely however, when I click to close it and then tell Windows to restart the program, it says:

Run-time error '-2147417848 (80010108):
Method 'Copy' of object 'Range' failed.

When I click "Debug", it highlights the following line.:

wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 1), wsDest1.Cells(1, lastColumnDest)).Copy wsDest1.Range(wsDest1.Cells(1, columnOfSAndP + 2), wsDest1.Cells(1, lastColumnDest + 1)) 'shift all columns from beyond columnOfSAndP by 1 column and place the name of the person from wsSrc.Cells(currentRowSrc, 11) into wsDest1.Cells(currentRowDest, columnOfSAndP+1)

Is there something wrong at all or does VBA take really long to work on three to four thousand cells and the error just arises because I cancelled the operation?

Edit: I am attaching the Excel workbooks (with no sensitive data) with the latest macro in it in case someone is reading this post for the first time (or for whichever convenience).

**Norie** · 11-14-2012, 05:37 AM

What, in words, is the code meant to do?

**s3a** · 11-14-2012, 11:34 AM

Running the macro should interpret data from the larger excel file/workbook and then transfer that interpretation to the smaller Excel file/workbook. The smaller file should eventually become larger than the currently larger file but I'll call "This workbook" or "the smaller file" since I'm referring to their state before the macro's first fully successful run.

What I mean by "interpretation", if it's not clear, is that although in some cases, data can be simply copied and pasted, in other cases, the destination data might be different (but based off of) the source data. For example, "this workbook" should look at the larger file and find out the hours someone is working (where the name of the person or another name but let's always consider it to be the name of the person) and put a W at every intersection of the time worked and the name of the person working. If the person doesn't work at a certain time/date, nothing will be put. Any letter other than W is to be put manually (without the use of the macro).

"This workbook" should be empty before the macro is run as of row 2 and below as well as have no names to the right of S&P, Loto Quebec, and Project.

For now, there is nothing to do in "this workbook" for "Resources required".

Date and Day should be copy/pasted. For every one row in the larger file, there needs to be 36 columns in the smaller file (from 6:00 AM to midnight).

The times are to be interpreted as the union (in math terminology) of "Reg Start" (column L in larger file) and "Reg End" (column M in larger file) as well as "Inst Start" (Column T in larger file) and "Inst End" (Column U in larger file).

Plan, Work, Flag, Sick, Vac, Training each don't need to be dealth with using the macro (since they use excel formulas or whatever the terminology is - the point is they change dynamically too and without a macro's help).

Everything on the right of "Project" is to be added there manually (without the help of the macro).

If skill is "LQ" in the larger file, the name of the person should go to the right of "Loto Quebec" in "this workbook". If it's anything else including S&P in the larger file, it should go in S&P in "this workbook".

Also, I would like for the process every time the macro is run to continue from the column after the last one processed in the larger file rather than going through the thousands of columns again (unnecessarily) and freezing the window (as well as taking long).

Hopefully, I didn't leave something out. If I find that I remember something I didn't say here, I will add it later.

If more information is needed, just ask.

**s3a** · 11-15-2012, 11:25 AM

Seeing as how the thread no longer reflects the post of the title, I will be marking this as solved and continuing the question anew from the following link.: http://www.excelforum.com/excel-prog...85#post3009385

Run-time error '438': Object doesn't support this property or method

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Re: Run-time error '438': Object doesn't support this property or method

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions