Posts by JohnCoulter

Sorry nesi, this ones' beyond me, it's possible to do this using vba, problem is I don't know how to read Hebrew, starting from scratch this is a rather large request imo, and if you're looking for porition to portion comparison it'd be even trickier

loop through cells

submit search page

select result and navigate

change portion size (that's what I'm assuming that drop down is) to predefined values (as I'm assuming you want the values to be comparable?)

I'd recommend you look into selenium wrapper or winhttp, automation via internetexplorer is an option also, though not as stable imo.

 wsOutput.Range("A3:N" & UBound(arOutput, 1) + 3).Value = arOutput()

should be

wsOutput.Range("A3:N" & UBound(arOutput, 1) + 2).Value = arOutput()

delete data on sheet2 prior to rerunning

to add a line break you could change

    Next lngDays

 Next lngRow

to

    Next lngDays
    
    lngCurrentOutputRow = lngCurrentOutputRow + 1
 
 Next lngRow

Here's my attempt using arrays, output formating isn't great though

Sub Macro2()
 Dim wsInput As Worksheet, wsOutput As Worksheet
 Dim xlnCalcMethod As XlCalculation
 Dim arRawData(), arOutput(), arTranspose()
 Dim lngRow As Long, lngDays As Long, lngCurrentOutputRow As Long
 Dim lngCol As Long
 
 With Application
 xlnCalcMethod = .Calculation
 .Calculation = xlCalculationManual
 .EnableEvents = False
 .ScreenUpdating = False
 End With
 
 Set wsInput = ThisWorkbook.Sheets("Sheet1") 'Name of input (source) data sheet. Change to suit.
 Set wsOutput = ThisWorkbook.Sheets("Sheet2") 'Name of output (destination) data sheet. Change to suit.
 
 ReDim arOutput(1 To 14, 1 To 1)
 
 arRawData = wsInput.Range("A3:M" & wsInput.Range("A:M").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row).Value
 
  For lngRow = LBound(arRawData, 1) To UBound(arRawData, 1)
 
    For lngDays = 1 To arRawData(lngRow, 12)
        
        ReDim Preserve arOutput(1 To 14, 1 To lngCurrentOutputRow + 1)
        
        For lngCol = LBound(arRawData, 2) To UBound(arRawData, 2)
        
            arOutput(lngCol + 1, lngCurrentOutputRow + 1) = arRawData(lngRow, lngCol)
        
        Next lngCol
        
        arOutput(1, lngCurrentOutputRow + 1) = lngDays
        arOutput(14, lngCurrentOutputRow + 1) = DateAdd("d", lngDays, arRawData(lngRow, 6))
        
        lngCurrentOutputRow = lngCurrentOutputRow + 1
    
    Next lngDays
 
 Next lngRow
 
 ReDim arTranspose(LBound(arOutput, 2) To UBound(arOutput, 2), LBound(arOutput, 1) To UBound(arOutput, 1))
 
 For lngRow = LBound(arOutput, 2) To UBound(arOutput, 2)
    For lngCol = LBound(arOutput, 1) To UBound(arOutput, 1)
        
        arTranspose(lngRow, lngCol) = arOutput(lngCol, lngRow)
    
    Next lngCol
 Next lngRow
 
 arOutput() = arTranspose()
 Erase arTranspose
 
 wsOutput.Range("A3:N" & UBound(arOutput, 1) + 2).Value = arOutput()
 
 wsOutput.Range("B2:N2").Value = wsInput.Range("A2:M2").Value
 wsOutput.Range("A2").Value = "ITEM"
 
 
 With Application
 .Calculation = xlnCalcMethod
 .EnableEvents = True
 .ScreenUpdating = True
 End With

Hi alhagag,

Would I be correct in saying that your code is supposed to loop through each row on Sheet1 and create as many rows as the value in column L and then have the Date column "N" on sheet2 be every date between Enter and Exit Date, and column A on Sheet2 be the current count of loop on the value in column L ?

it's possible to build a vba macro to navigate to a website and enter data into a form and submit that form and return data from that website to the spreadsheet, what website are you referring to?

Sorry to waste anyones time who's already spent time looking at this, but I believe the issue is related to Access rounding the results before running them through mod, as Access Mod only allows Ints. So (3/(2^1) aka 1.5 becomes 2 and 2 mod 2 = 0. solution to make access behave like excel is:

(Int(ModuleBitWise/(2^0)) Mod 2) >= 1. This Int() forces the result of (ModuleBitWise/(2^0)) to be roundeddown to the closest whole number

Thanks

Nick

Hi All,

I've come across a really confusing problem, when I was attempting to build a workaround to the lacking Bitwise AND in access.

In excel - formulas in the header

In Access using the same data in column A but in an access table:

SELECT ModuleBitWise,

((ModuleBitWise/(2^0)) Mod 2) >=1 as Result0,

((ModuleBitWise/(2^1)) Mod 2) >=1 as Result1

((ModuleBitWise/(2^2)) Mod 2) >=1 as Result2

((ModuleBitWise/(2^3)) Mod 2) >=1 as Result3

FROM STATIC_DATA

I get the below:

Does anyone know what's going on here? I'm stumped and been looking at this so long that my brain is now mush. Excel is behaving as expected however Access isn't.

Thanks,

Nick