Posts by xl factor

Hello experts,

I am trying to automate a chrome packaged desktop trading application using selenium.
I can find help for automation of chrome browser, but not for stand alone chrome package apps. If anyone could point me out to any resources on this will be great.

Thank you and Regards,
XL Factor

Hi Kenneth,

Thank you for your quick response. Really appreciate your help.
Will give it a go next week.

Dear Kenneth,

Thank you for your response.
I am aware that sendkeys is always an option that one can fall back on in such situations.

However I am still seeking to know weather or not, it is possible to control the Save as Dialog box already open by IE, just as it is controlled in the Excel environment.

like:

Dim xyz as FileDialog


set xyz = (hWnd) ' handle number of the IE Save as window

Hi,

I am running a vba code from excel to navigate to a webpage and save an .xls report.
I have managed to reach to the point where the "FileDialog(msoFileDialogSaveAs)" is opened by IE. I also am able to click the save button using sendMessage() API.
However, I cant seem to figure how to change the name of the file.

Is it possible to set the HWND of the already open dialogue box be set as FileDialog object and control it?

Thank you.

Re: count rows if conditions are true in 3 columns

Hi XOR LX,

Thank you for your response. Just what I need.

Best Regards

Hi,

I need to count the number of rows if condition is true in 3 columns.
Currently I am using the below formula, but I need to sum for almost 500 odd rows. How can this be done using array formulas.

Any Ideas??

Quote

=IF(AND(B4="B1",F4="D",AM4="X"),1,0)

Thanks and Regards,
XL Factor

Re: calculate range without looping

Hi TheGlovner,

The reason I was not in favour of a loop is because, the UDF's themselves have a loop, so I was trying to avoid looping inside a loop. The data on my sheet was very vastly spread out as I was initially planning a yearly Roster on the same sheet.

I am now planning a monthly roster, with the UDF's in sheets, entered as formulas. When I hit Calculate sheet, It still takes a good 5 minuits to finish calculating.

Is there a better way to achieve what I am trying to do?

I have attached a sample workbook. Password for the VBA project is "formula"

Thank you very much for your time. I really appreciate your help.

Regards,
xl factor.

Re: calculate range without looping

Hi TheGlovner,

The reason I was not in favour of a loop is because, the UDF's themselves have a loop, so I was trying to avoid looping inside a loop. The data on my sheet was very vastly spread out as I was initially planning a yearly Roster on the same sheet.

I am now planning a monthly roster, with the UDF's in sheets, entered as formulas. When I hit Calculate sheet, It still takes a good 5 minuits to finish calculating.

Is there a better way to achieve what I am trying to do?

I have attached a sample workbook.

Thank you very much for your time. I really appreciate your help.

Regards,
xl factor.

Hi All,

Im trying to calculate a range using a UDF. It works fine for Range("F632").

Range("F632:NF632").value = CountTitles(Range("B619"), Range("B605"), Range("F4:F502"), Range("B4:B502"))

But when the UDF calculates for Range("G632"), I need to have the Range("G4:G502") instead of Range("F4:F502") and so on.

I know this can be achieved by using

.formula ="=enter formula"

But im just wondering if the former approach is possible without having to run a loop??

Any Ideas..

Regards,
XL Factor

Dear experts,

I have an excel report generated by the system. All timings on the sheet is stored as text. I need to perform calculations with these values and need a macro to convert the text from 5:00 to 05:00.

Attached is a system generated excel report.

Thank You.

Re: Run-time error '1004' : Method 'Range' of object'_Global' failed

Dear Grimes0332,

Thank you for the quick one liner insight. That fixed my mysery.

Best Regards,

Re: Run-time error '1004' : Method 'Range' of object'_Global' failed

Quote from Grimes0332;768214

LastCell is not initialised.

How can I fix that? Sorry I'm not an expert in VBA

Dear Experts,

The below code works fine when run from the worksheet object.

Private Sub Worksheet_Activate()
Dim movRange As Range
Dim PasteLoc As Range
Dim lastCell As Range
Set movRange = Sheets("CALC").Range("A542:BC956")
Set PasteLoc = ActiveSheet.Range("A542:BC956")
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)




movRange.Copy Destination:=PasteLoc








ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address




End Sub

How ever when I run it from a userform option button

Private Sub OptionButton1_Click()




'INTO MANUAL MODE




Application.EnableEvents = False
Dim ws As Worksheet
Dim movRange As Range
Dim PasteLoc As Range
Dim lastCell As Range
Set ws = ActiveSheet
Set movRange = Sheets("CALC").Range("A542:BC956")
Set PasteLoc = ActiveSheet.Range("A542:BC956")
'Set lastCell = Cells.SpecialCells(xlCellTypeLastCell)
movRange.Copy Destination:=PasteLoc












ws.PageSetup.PrintArea = ws.Range(Cells(1, 1), lastCell).Address
















Unload UserForm1




End Sub

I get the subject error.
The debugger highlights the following line

ws.PageSetup.PrintArea = ws.Range(Cells(1, 1), lastCell).Address

Any Ideas?

Re: VBA to calculate the number of days in a month based on ActiveSheet name

Dear Smallman,

Thank you for the above code. It works well.
Would it be possible to have the Year input by an input box? as the year would not necessarily be the current year?

Dear Max1616, your code works correctly except for FEB where it calculates as 31.

Regards

Hi All,

I'm running a vba code in the active sheet on a workbook. All sheet names are JAN, FEB, MAR and so on.
What I need is a line of code to determine how many days are there in that month based on the name of the ActiveSheet.

Any ideas? Below is the code.

Private Sub CommandButton1_Click()
On Error Resume Next
    Dim i As Integer
    Dim m As Integer
    Dim h As Integer
    Dim j As Integer
mr = InputBox("Enter Staff No.")
If mr = "" Then Exit Sub 'user canceled
If Columns(4).Find(mr, , , xlWhole) Is Nothing Then
    MsgBox "No Staff Number match for " & mr, vbExclamation, "Invalid Staff Number"
    Exit Sub
End If
mc = InputBox("Enter Date")
i = InputBox("Enter number of days")
m = Range("A2").Value
If Not (32 - i - mc) > 0 Then
h = m - mc
Cells(Columns(4).Find(mr).Row, Rows(2).Find(mc).Column).Select
Selection.Resize(, h).Select
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
With Selection
    .SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(217, 217, 217)
    .SpecialCells(xlCellTypeBlanks).Merge
    .SpecialCells(xlCellTypeConstants).Interior.Color = RGB(83, 142, 213)
    .SpecialCells(xlCellTypeConstants).Merge
    .ClearContents
    .Font.Color = RGB(0, 0, 0)
End With
j = i - h
MsgBox ("You still need to book " & j & " day(s) more on next month")
With UserForm1
Unload Me
End With
Else
Cells(Columns(4).Find(mr).Row, Rows(2).Find(mc).Column).Select
Selection.Resize(, i).Select
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
With Selection
    .SpecialCells(xlCellTypeBlanks).Interior.Color = RGB(217, 217, 217)
    .SpecialCells(xlCellTypeBlanks).Merge
    .SpecialCells(xlCellTypeConstants).Interior.Color = RGB(83, 142, 213)
    .SpecialCells(xlCellTypeConstants).Merge
    .ClearContents
    .Font.Color = RGB(0, 0, 0)
End With
Application.ScreenUpdating = True
With UserForm1
Unload Me
End With
End If
End Sub

Re: Copy column values to another sheet based on input box value

Hey Guys,

Below is the final code that also retains the formatting.

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim HEXcolor As String
    Dim RGBcolor As String
    Dim rng As Range
    Dim foundDate As Range
    Dim response As String
    response = InputBox("Enter date between 1 and 31.")
    Set foundDate = Range("F2:AJ2").Find(response, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundDate Is Nothing Then
        For Each rng In Range(Cells(4, foundDate.Column), Cells(LastRow - 1, foundDate.Column))
            HEXcolor = Right("000000" & Hex(rng.Interior.Color), 6)
            RGBcolor = "RGB (" & CInt("&H" & Right(HEXcolor, 2)) & ", " & CInt("&H" & Mid(HEXcolor, 3, 2)) & ", " & CInt("&H" & Left(HEXcolor, 2)) & ")"
            If CInt("&H" & Right(HEXcolor, 2)) = 252 And CInt("&H" & Mid(HEXcolor, 3, 2)) = 213 And CInt("&H" & Left(HEXcolor, 2)) = 180 Then
                Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = rng
                    Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Interior.Color = rng.Interior.Color
                            Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Font.Color = rng.Font.Color
                Range("C" & rng.Row & ":D" & rng.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            End If
        Next rng
    End If
    Application.ScreenUpdating = True
End Sub

Re: Copy column values to another sheet based on input box value

Dear Mumps,

Thank you for the quick fix.
Would it also be possible to retain the formatting for column C in sheet2 (fill colors and font colors).
I'm still happy with the above code. It works perfectly.

Re: Copy column values to another sheet based on input box value

Dear Mumps,

Thank you for your time and the above code. I am grateful for the same.
The code works well to copy the data from Columns C & D into sheet2 columns A & B

However, in sheet2 in column C the following text gets pasted throughout the column "RGB (252,213,180)". Whereas It needs to return the values & formats from the column of the Inputbox.

I cant seem to figure from the code above as it is beyond my limited knowledge. Any ideas?

Hi All,

I have a workbook with 12 sheets named JAN, FEB, MAR etc.
Each sheet is a Roster sheet that has dates in row 4 starting from F2 being 1, G2 being 2 and so on till AJ2 being 31.
Column C & D have names and staff numbers.
The last row to be used in this code is a merged row in column A that has the text "END OF ROSTERS" (Minus one row).

Attached is a downsized version of the roster.

I need a vba code to do the following:

Bring on an input box and ask to enter a date (anything between 1 to 31).
Based on the entry in the input box choose the correct column from F4 to AJ4 till the row in column A that has the text "END OF ROSTERS" (Minus one row).
Loop the column to find if the cell interior color is RGB(252, 213, 180), if yes, then copy that value along with values in C & D in the same row, loop untill sheet1 has a merged row in column A that has the text "END OF ROSTERS" (Minus one row).

Thank you.

Re: count the number of intersection of two columns (color and text)

Hi Guys,

The solution to the above is at. Many Thanks.
http://www.excelforum.com/exce…lor-text.html#post4299258