PDA

View Full Version : IF LOOP FOR LOOP AND IMPORTING FROM ANOTHER WORKBOOK



ytjjjtyj
08-02-2019, 01:01 PM
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. )

mana
08-02-2019, 08:44 PM
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

ytjjjtyj
08-05-2019, 04:39 PM
Thank you Mana, this finally worked!
I was initially having trouble with defining ws1 and ws2 but it finally worked!