Consulting

Results 1 to 3 of 3

Thread: IF LOOP FOR LOOP AND IMPORTING FROM ANOTHER WORKBOOK

  1. #1
    VBAX Regular
    Joined
    Jun 2019
    Posts
    50
    Location

    IF LOOP FOR LOOP AND IMPORTING FROM ANOTHER WORKBOOK

    Hello I really need help.

    Nothing is working but I will explain what I am trying to do.

    In "book1-VBA" I am trying to see if the numbers in column D are equal to or over 24. If they are, then put it in a variant and round UP the value in that row in column B then round DOWN the value in that row in column C.

    After that is stored in a variant I want to close book1-VBA and open book2-VBA and get the start and end time numbers and the numbers between them to match the number in my named range in book2-VBA called "MyNumbers". If it matches- then remove the highlight to be white. If there is no match, then do nothing.

    Can anyone give this a shot? Thank you in advance.

    (If there is a better way to do this without storing in a variant and then importing it to make it useful I would be happy to hear it. )
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    Option Explicit
    
    
    Sub test()
        Dim dic As Object
        Dim wsf As Object: Set wsf = WorksheetFunction
        Dim p As String
        Dim ws1 As Worksheet
        Dim ws2 As Worksheet
        Dim v
        Dim j As Long, k As Long
        Dim c As Range
        
        Set dic = CreateObject("scripting.dictionary")
        p = ThisWorkbook.Path & "\"
        Set ws1 = Workbooks.Open(p & "Book1-VBA.xlsm").Worksheets("_Summary")
        Set ws2 = Workbooks.Open(p & "Book2-VBA.xlsm").Worksheets("sheet1")
        
        v = ws1.Columns(1).SpecialCells(xlCellTypeConstants).Resize(, 4).Value
        ws1.Parent.Close False
        
        For k = 1 To UBound(v)
            If v(k, 4) > 24 Then
                For j = wsf.RoundUp(v(k, 2), 0) To wsf.RoundDown(v(k, 3), 0)
                    dic(j) = Empty
                Next
            End If
        Next
        
        For Each c In ws2.Rows(1).SpecialCells(xlCellTypeConstants)
            If dic.exists(c.Value) Then
                c.Offset(1).Interior.ColorIndex = xlNone
            End If
        Next
    
    
    End Sub
    Last edited by mana; 08-02-2019 at 10:20 PM.

  3. #3
    VBAX Regular
    Joined
    Jun 2019
    Posts
    50
    Location
    Thank you Mana, this finally worked!
    I was initially having trouble with defining ws1 and ws2 but it finally worked!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •