Search:

Type: Posts; User: Yongle; Keyword(s):

Page 1 of 6 1 2 3 4

Search: Search took 0.10 seconds.

  1. Re: Runtime error 91 Object Variable or with block variable not set

    what type of value is being searched for ?
    - string ?
    - numeric ?
    - boolean ?
  2. Re: Need some help with what I thought was a basic IF formula

    quote marks are the cause of your problem
    =IF((AZ2<$AX$7),(""),(("BA2/AZ2)*100")))

    =IF((AZ2<$AX$7),(""),((BA2/AZ2)*100))

    removing brackets not required too..
    =IF(AZ2<$AX$7,"",BA2/AZ2*100)
  3. Replies
    13
    Views
    183

    Re: More =IF formula issues

    Try replacing

    ws.Range("D3:H52").Value2 = ws.Range("D3:H52").Value2

    with

    With ws.Range("D3:H52")
    .NumberFormat = "General"
    .Value2 = .Value2
    End With
  4. Replies
    13
    Views
    183

    Re: More =IF formula issues

    It's a workaround - and makes the problem go away give you a change to deal with the problem
    (How much time does it take to amend a few formulas compared to the fallout of not doing so?)


    agreed...
  5. Replies
    2
    Views
    45

    Re: workbooks.open with file browser

    or ...


    Sub FilePickerMethod()
    Dim xfile As String
    With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = "C:\Folder\SubFolder\" 'Set the initial...
  6. Replies
    13
    Views
    183

    Re: More =IF formula issues

    If you are getting inconsisitent values in cells (sometimes boolean, sometime text), then how about using this method ...

    =IF(OR('Table'!G20=TRUE,'Table'!G20="TRUE"),"YES","NO")
  7. Replies
    13
    Views
    183

    Re: More =IF formula issues

    Test the type of value in G20 with

    =CELL("type",G20)

    If it returns V it is boolean, if it returns L it is text
  8. Replies
    13
    Views
    183

    Re: More =IF formula issues

    @jmacleary - are you sure ?

    If true is typed into G20, the value returned is centre-aligned and changes to TRUE
    Text would be left-aligned and left unchanged as true

    (unless cell number format...
  9. Re: Check if date is in future and empty textbox

    another option which should work provided that your textbox format is consistently mm/dd/yyyy


    call the function like this

    txtStDate.Value = ReverseDate(txtStDate.Text)
  10. Re: Check if date is in future and empty textbox

    Please explain exactly how the value gets into textbox txtStDate
  11. Replies
    1
    Views
    81

    Re: adding 1 to a time (cross mid night)

    Time is a decimal less than 1
    Dates are integers
    Adding 1 to a time is adding one day to it - which is what happens when midnight is crossed

    Try this..
    In A1 enter formula
    =NOW()
    In A2 ...
  12. Replies
    2
    Views
    98

    Re: VBA code for undo

    Application.Undo

    but it use is very limited
    see https://docs.microsoft.com/en-us/office/vba/api/excel.application.undo
  13. Re: Check if date is in future and empty textbox

    May be due to the old chestnut AMERICAN dates vs UK dates

    Test by inserting one line of code and inputting values where day and month are different to each other and both below 12

    11 October...
  14. Re: formula to get the value of the same cell but in different sheets

    :oops: the formulas are in B1 and B3 copied across in above example
  15. Re: formula to get the value of the same cell but in different sheets

    and developing on previous post ...
    the names of the sheets can be autogenerated with this trick

    Create a named Range with
    Name: ListSheets
    RefersTo:...
  16. Replies
    4
    Views
    101

    Re: Macro to cut and paste to master file

    Modify your recorded macro to open the master workbook and do what you need, something like this ..



    Dim cel As Range, rng As Range, wb As Workbook

    'YOUR current code goes here
    ...
  17. Replies
    8
    Views
    197

    Re: Application.GetOpenFilename error

    I am glad that your question was answered:rolleyes:
  18. Replies
    8
    Views
    197

    Re: Application.GetOpenFilename error

    It is not a question mark - it is an unprintable character manifesting itself as a question mark

    Hopefully this method will work ..
    Identify nuisance character's ascii number using the ASC...
  19. Replies
    12
    Views
    254

    Re: very hidden sheets add-in

    Ok
    I am busy for the next 48 hours - look for update after that
  20. Replies
    12
    Views
    254

    Re: very hidden sheets add-in

    So you would like a column with toggle on\off for sheet protection ?

    Password will be the issue
    - are passwords identical for all sheets in the same workbook ?
  21. Replies
    12
    Views
    254

    Re: very hidden sheets add-in

    Is there any functionality missing ?
  22. Replies
    12
    Views
    254

    Re: very hidden sheets add-in

    I have cobbled this together very quickly just to see if it is giving you the basic functionality
    If it is broadly what you want then I will tidy it up and convert into an addin later
    It is...
  23. Replies
    12
    Views
    254

    Re: very hidden sheets add-in

    Would this do what you want ?

    An icon in the ribbon area available regardless of which workbook is currently active
    Clicking on the icon would list current status of all sheets in active workbook...
  24. Thread: VBA Formula

    by Yongle
    Replies
    31
    Views
    636

    Re: VBA Formula

    or ...

    BEFORE

    Excel 2016 (Windows) 32 bit

    A
    B
    C
    D
  25. Replies
    8
    Views
    197

    Re: Application.GetOpenFilename error

    An alternative method to browse to the file
    - does it result in same problem ??



    Sub AnotherWay()
    Dim MyFile As String
    With Application.FileDialog(msoFileDialogFilePicker)
    ...
Results 1 to 25 of 150
Page 1 of 6 1 2 3 4