Posts by Binning

  • code error in macro

    Re: code error in macro


    Do you want Tim1 and Tim2 to be equal to the values on Cells A1 and B1? If so it looks like you've got this back to front


  • Manipulating Combobox in Internet Explorer

    Re: Manipulating Combobox in Internet Explorer


    Hi Jon,


    I finally managed to resolve this


    Code
    ie.document.getElementsByTagName("button")(12).Click
   
   Set table = ie.document.getElementById("FMT_TYP_CDTable")
   table.Rows(3).Cells(0).Click


    My hunch was correct, the element only comes into existence when the dropdown button is activated. I had to go through all the buttons to figure out which one was correct. Everything is now working as it should, thanks for your help!

  • Manipulating Combobox in Internet Explorer

    Re: Manipulating Combobox in Internet Explorer


    Tend to run line by line anyway. I can only seem to find the table reference when I inspect the element. The raw HTML data for the page doesn't seem to make any reference to "FMT_TYP_CDTable". Would this have any impact? It seems to adjust the values in the other text boxes without any issue.


    Is it possible that the element only comes into existence once the dropdown box has been activated?

  • Manipulating Combobox in Internet Explorer

    Re: Manipulating Combobox in Internet Explorer


    Quote from John_w;755314

    You need to set a reference to MS HTML Object Library in the VBA editor Tools -> References menu.


    I believe this is already the case, otherwise the HTMLTable dimension would flag an error? The error happens on the ie.document.getelementbyid() line. I have Microsoft HTML Object Library selected. Is there anything else I should have activated?

  • Using Macro to reconcile different sheets

    Re: Using Macro to reconcile different sheets


    The best way to do it would be to have separate sheets for each of the components (i.e. a sheet for Manager, a sheet for Employee Name etc.). Then follow the below (using Manager as an example, but repeat the same process for the rest).


    1. Create a master list of all managers and list them in a column with header "ALL MANAGERS"
    2. Copy and paste all the managers from each sheet into this column, then remove duplicates (select range then ALT+A+M)
    3. Create more columns to reference the multiple sheets (i.e. "Managers Sheet 1", "Managers Sheet 2", etc. etc.)
    3. Use the MATCH function to reconcile the managers in the master list with all the sheets. MATCH will return a row number if found, and an #N/A if not


    I have attached a spreadsheet as an example.

  • Workbook automatically saving in different places problem

    Re: Workbook automatically saving in different places problem


    It will just be defaulting to the last place you saved an excel sheet.


    Put this line at the top of your code


    Code
    Dim relativePath As String


    And have this in place of your save line.


    Code
    relativePath = ThisWorkbook.Path & "\" & ActiveWorkbook.Name
    ActiveWorkbook.SaveAs Filename:=relativePath


    This will ensure it saves in the current location.

  • Manipulating Combobox in Internet Explorer

    Hi all,


    I've been having trouble trying to manipulate a dropdown/combobox in internet explorer. The combo box looks as below


    [ATTACH=CONFIG]66743[/ATTACH]


    I can see the HTML for the element reads as below



    I'm attempting to select EMAIL from the dropdown box. I can change the value of FMT_TYP_CDtxt to EMAIL, however when I submit this it reverts back to default. I have also noticed that I'm able to change the value to anything, even if it is not in the dropdown list. This makes me think that referencing FMT_TYP_CDtxt is incorrect.


    Please see my code so far






    I'm literally at the end of my tether and I would appreciate any assistance massively. This is the last part of the process and it's killing me.


    Thanks,
    Binning

  • Whole number minus whole number = decimal number?

    Hi there,


    I'm currently facing an error where all zero values should automatically revert to a "-". This is working fine for all cells, however I'm having an error with one in particular. Currently the value in E17 is 8,769.60 and the value in E34 is 8,769.60. There are no more decimals after these figures. When I do one less the other, excel returns a value of "0.00". When I expand this value, it shows up 0.0000000014897523215.


    Is there any way to stop this from happening? I have tried formatting the cells, putting in the values manually etc. but I still get the same problem.


    Thanks,
    Kieran

  • Change Multiple "Refers To" in Name Manager at once

    Re: Change Multiple "Refers To" in Name Manager at once


    Quote from royUK;732962

    If you really have millions of cells with formatting that is probably the cause of your problem.


    Do all names refer to the same 'Raw Data Balances'?


    Haven't you got a back up copy from before the error occurred?


    Hi Roy,


    The majority of the names refer to 'Raw Data Balances'. It's a pretty poorly designed spreadsheet and this problem is encountered a lot. I've tried almost all avenues for removing the formatted cells causing the issue but with little success. For the mean time we're using back up and inputting the data manually, I think this is really our only option at this point.


    Thanks,
    Binning

  • Change Multiple "Refers To" in Name Manager at once

    Hi there,


    I'm currently facing an issue whereby a raw data worksheet that a lot of other sheets make reference to has become corrupt (formatting on millions of cells and unable to delete the rows / columns without excel showing a message that there are unavailable resources). I'm doing this on a company computer so there is no opportunity for me to download any sort of add-in for to make the adjustments.


    Currently, the "Refers To" in the Name Manager is listed as 'Raw Data Balances'. Since I have made a copy of the sheet to try and salvage the data, I would like to change it to 'Raw Data Balances(2)'.


    This is the only way I imagine it can be fixed, as deleting the sheet entirely will mess up the formulas everywhere else. If you have any other suggestions please let me know.


    Thanks,
    Binning

  • Macro for splitting sheet

    Re: Macro for splitting sheet


    Inski,


    Have you ensured that you have a Sub statement at the start of the routine?


    Paste this into the top of the code that has been listed above:


    Code
    Sub Transfer_USD()


    From looking at the code above, it is not very relevant to what you are trying to do. I think it's just something for you to borrow from.

  • VBA Excel Advanced Filter

    Re: VBA Excel Advanced Filter


    [INDENT].... i think you've got the comma and the decimal mixed up.[/INDENT]


    39,640.86 is correct. 39.640,86 is not. excel will recognise the first number as a number, but the second it will identify as text.

  • VBA Excel Advanced Filter

    Re: VBA Excel Advanced Filter


    Because each of the entires contains "EUR", it will not be able to identify the preceding number as a number. You can only filter on values with a range if the cells only contain numbers. Do you need to have "EUR" at the end of each item? Can you perhaps convert this to currency?


    If this is an issue then you can just hide all the rows which don't meet the criteria (which is effectively what a filter does anyway)


    Code
    Sub Filter_Euros()
Dim LR As Double
Dim i As Double
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To LR
If Left(Range("A" & i).Value, 4) > 100 Or Left(Range("A" & i).Value, 4) < 50 Then Rows(i).Hidden = True
Next i


End Sub


    This assumes the Gesamtwert column is A and that the first values is in Row 2. It will hide all values >100 Or <50