none
Problem to the line RRS feed

  • Question

  • Hi,
    I get the attached problem below


    due to the last line of these codes below.

        Dim strSearch As String
        Set rng1 = ws.Range("A:A")
        Dim cell As Range
        
        Dim ConfigWS As Worksheet
        Set ConfigWS = Worksheets("config")
     
        For i = 2 To ConfigWS.Cells(Rows.Count, "C").End(xlUp).Row
            strSearch = ConfigWS.Cells(i, "C")
            
            Set cell = ActiveSheet.Cells(ConfigWS.Cells(i, "D"), 3)
            ...
    why?

    Many Thanks & Best Regards, Hua Min



    • Edited by Jackson_1990 Friday, September 11, 2015 10:00 AM
    Friday, September 11, 2015 9:55 AM

Answers

  • Above the problem line

            Set cell = ActiveSheet.Cells(ConfigWS.Cells(i, "D"), 3)

    temporarily insert the line

            MsgBox ConfigWS.Cells(i, "D").Value

    If everything is OK, the message box should display a valid row number.

    If not, that causes the error. You'll have to look at the Config sheet to check the values in column D.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 11, 2015 3:18 PM

All replies

  • Does it work better if you use

            Set cell = ActiveSheet.Cells(ConfigWS.Cells(i, "D").Value, 3)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 11, 2015 11:29 AM
  • Thanks.

    I still get the same problem, after having put your codes.


    Many Thanks & Best Regards, Hua Min

    Friday, September 11, 2015 1:14 PM
  • Could you post the complete code?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 11, 2015 2:23 PM
  • Here are the complete codes of the event

    Public Function Refresh()
        ExactLogined = False
        ExactLoginForm.Show
        
        If ExactLogined = False Then
            Exit Function
            End If
        
    ''On Error GoTo err:
       
        Dim rowNum As Integer
        Dim lastRowNumAccumOrder As Integer
        Dim lastRowNumAccumAmount As Double
        
        lastRowNumAccumOrder = 0
        lastRowNumAccumAmount = 0
        
        For rowNum = 5 To 46
            Dim thisDate As Date
            If (IsDate(ActiveSheet.Cells(rowNum, 1)) And ActiveSheet.Cells(rowNum, 2).Value = "") Then
                thisDate = ActiveSheet.Cells(rowNum, 1)
                
                Dim rs As New ADODB.Recordset
                Dim sql As String
                
                ''sql = "exec csp_OrderBookingPerMonth '" & thisDate & "'"
                sql = "exec csp_OrderBookingPerMonth '" & Format(thisDate, "d/m/yyyy") & "'"
                
                rs.Open sql, ExactConn
                
                If (rs.Fields(0).Value > 0) Then
                    ''ActiveSheet.Cells(rowNum, 2) = rs.Fields(0).Value
                    ActiveSheet.Cells(rowNum, 2) = rs.Fields(1).Value - lastRowNumAccumOrder
                    ActiveSheet.Cells(rowNum, 3) = rs.Fields(1).Value
                    ''ActiveSheet.Cells(rowNum, 4) = rs.Fields(2).Value / 7.8 / 1000
                    ActiveSheet.Cells(rowNum, 4) = (rs.Fields(3).Value / 7.8 / 1000) - lastRowNumAccumAmount
                    ActiveSheet.Cells(rowNum, 5) = rs.Fields(3).Value / 7.8 / 1000
                    ''ActiveSheet.Cells(rowNum, 7) = rs.Fields(2).Value / rs.Fields(0).Value / 7.8 / 1000
                    ActiveSheet.Cells(rowNum, 7) = "=IF(B" & rowNum & "=0,0, D" & rowNum & "/B" & rowNum & ")"
                ''ElseIf (rs.Fields(0).Value = 0 And ActiveSheet.Cells(rowNum, 1)) Then
                ElseIf (rs.Fields(0).Value = 0 And Format(ActiveSheet.Cells(rowNum, 1), "yyyy-mm-dd") < Format(Now, "yyyy-mm-dd")) Then
                    ActiveSheet.Cells(rowNum, 2) = 0
                    ''MsgBox (Format(ActiveSheet.Cells(rowNum, 1), "yyyy-mm-dd"))
                   '' MsgBox (Format(Now, "yyyy-mm-dd"))
                Else
                    ''ActiveSheet.Cells(rowNum, 5) = rs.Fields(3).Value / 7.8 / 1000
                End If
                                        
                rs.Close
                
            End If
            
            If (IsDate(ActiveSheet.Cells(rowNum, 1)) And ActiveSheet.Cells(rowNum, 3).Value <> "") Then
                lastRowNumAccumOrder = ActiveSheet.Cells(rowNum, 3)
                lastRowNum0AccumAmount = ActiveSheet.Cells(rowNum, 5)
                ''MsgBox (lastRowNumAccumOrder & " -  " & lastRowNumAccumAmount)
            End If
          
        Next rowNum
        
        Dim rs2 As ADODB.Recordset
        Dim sql2 As String
        
        Set rs2 = New ADODB.Recordset
        sql2 = "exec csp_dailySalesReport2015 '" & Format(thisDate, "dd/m/yyyy") & "'"
        
        rs2.Open sql2, ExactConn
        
        Dim ws As Worksheet
        Dim i As Integer
        
        Set ws = Worksheets("data2014")
        i = 1
        
        ws.UsedRange.Clear
        
        ws.Cells(i, 1) = "Classification"
        ws.Cells(i, 2) = "CustomerCode"
        ws.Cells(i, 3) = "CustomerCode2"
        ws.Cells(i, 4) = "SelCode"
        ws.Cells(i, 5) = "SalesCode"
        ws.Cells(i, 6) = "SalesPerson"
        ws.Cells(i, 7) = "Country"
        ws.Cells(i, 8) = "District1"
        ws.Cells(i, 9) = "District2"
        ws.Cells(i, 10) = "Order#"
        ws.Cells(i, 11) = "Order Date"
        ws.Cells(i, 12) = "CS"
        ws.Cells(i, 13) = "OrderAmountUSD"
        i = i + 1
        
        Do While Not rs2.EOF
            ws.Cells(i, 1) = rs2.Fields(0).Value
            ws.Cells(i, 2) = rs2.Fields(1).Value
            ws.Cells(i, 3) = rs2.Fields(2).Value
            ws.Cells(i, 4) = rs2.Fields(3).Value
            ws.Cells(i, 5) = rs2.Fields(4).Value
            ws.Cells(i, 6) = rs2.Fields(5).Value
            ws.Cells(i, 7) = rs2.Fields(6).Value
            ws.Cells(i, 8) = rs2.Fields(7).Value
            ws.Cells(i, 9) = rs2.Fields(8).Value
            ws.Cells(i, 10) = rs2.Fields(9).Value
            ws.Cells(i, 11) = rs2.Fields(10).Value
            ws.Cells(i, 12) = rs2.Fields(11).Value
            ws.Cells(i, 13) = rs2.Fields(12).Value
            
            i = i + 1
            rs2.MoveNext
        Loop
        rs2.Close
        
        Dim OrderCount As Integer
        Dim OrderAmount As Double
     
        Dim rng1, rng2 As Range
        Dim strSearch As String
        Set rng1 = ws.Range("A:A")
        Dim cell As Range
        
        Dim ConfigWS As Worksheet
        Set ConfigWS = Worksheets("config")
     
        For i = 2 To ConfigWS.Cells(Rows.Count, "C").End(xlUp).Row
            strSearch = ConfigWS.Cells(i, "C")
            ''MsgBox (i & " " & strSearch)
            
            Set cell = ActiveSheet.Cells(ConfigWS.Cells(i, "D"), 3)
            
            ''OrderCount = WorksheetFunction.CountIf(ws.Range("A:A"), "=Asia")
            ''OrderAmount = WorksheetFunction.SumIf(ws.Range("A:A"), "=Asia", ws.Range("M:M"))
            
            OrderCount = WorksheetFunction.CountIf(ws.Range("A:A"), "=" & strSearch)
            OrderAmount = WorksheetFunction.SumIf(ws.Range("A:A"), "=" & strSearch, ws.Range("M:M"))
            
            ''MsgBox (strSearch & " " & OrderCount & "" & OrderAmount)
            
            cell = OrderCount
            cell.Offset(0, 1) = OrderAmount
            cell.Offset(0, 2) = "='monthly target'!" & ColumnIndex2Letter(2 + Format(thisDate, "m")) & ConfigWS.Cells(i, "E") & "/1000"
            
            OrderCount = 0
            OrderAmount = 0
    
            ''Set rng2 = rng1.Find(strSearch, , xlValues, xlWhole)
            
            ''If Not rng2 Is Nothing Then
            ''    cell = rng2.Offset(0, 1)
            ''    cell.Offset(0, 1) = rng2.Offset(0, 2)
            ''Else
            ''    cell = 0
            ''    cell.Offset(0, 1) = 0
            ''    cell.Offset(0, 2) = 0
            ''End If
             
            ''cell.Offset(0, 2) = "='monthly target'!" & ColumnIndex2Letter(2 + Format(thisDate, "m")) & ConfigWS.Cells(i, "E") & "/1000"
        Next i
              
              
    ''Exit Function
    ''err:
    ''MsgBox ("Application Error")
    End Function
    


    Many Thanks & Best Regards, Hua Min

    Friday, September 11, 2015 3:06 PM
  • Above the problem line

            Set cell = ActiveSheet.Cells(ConfigWS.Cells(i, "D"), 3)

    temporarily insert the line

            MsgBox ConfigWS.Cells(i, "D").Value

    If everything is OK, the message box should display a valid row number.

    If not, that causes the error. You'll have to look at the Config sheet to check the values in column D.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, September 11, 2015 3:18 PM
  • Many thanks.
    Can I know what value

    ConfigWS.Cells(Rows.Count, "C").End(xlUp).Row

    is going to have, each time?

    Many Thanks & Best Regards, Hua Min

    Monday, September 14, 2015 1:01 AM
  • It's the row number of the last non-empty row in column C on the Config worksheet.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, September 14, 2015 5:50 AM