Posts by lasw10

  • Business Days - holidays and weekends

    Re: Business Days - holidays and weekends


    as an aside... would anything like this be viable for you?


    unlikely if you really need the matrix as per your other sheet - this way shows how to use a pivot table instead ... do your calcs as additional fields - no requirement for VBA.


    that said I will come up with an alternative.


    do you have a list of the dates you want excluded from the workingdays calcs - ie those holidays falling midweek?


    if so can you provide us with it - XL will not be able to establish these dates itself ... it is not set up with public holiday info.

  • Business Days - holidays and weekends

    Re: Business Days - holidays and weekends


    with networkdays functions you specify the exception dates (not XL) so you just list those days you don't want it to use that are otherwise Mon-Fri dates.


    If this is too simplistic please post a scaled down version of your file with sample data so we can provide a VBA solution if required.

  • Daily updated information placed in cells by month

    Re: Daily updated information placed in cells by month


    hey - can you attach your file - kinda difficult to follow what it is your trying to do ...


    sounds feasible enough...


    also in terms of issue #1 you could use the ontime method but my preference is just to use Windows built in scheduler to launch specific file - then just put a trigger in the workbook_open event to fire your routines as required before closing the file and killing the app.

  • Loops

    Re: Loops


    yep the underscore tells VBA that what appears on the next line down in the code is essentially the same line...


    so in this instance you dont' want it because you have more than 1 action to do if the result is true hence


    Code
    if x = y then
 'action1
 'action2
end if


    if you had one action to do (like font.bold the cell) then you would do it one line as follows


    Code
    if x = y then action1


    you only need the End If if you split across +1 line


    typing:

    Code
    if x = y then _
action1
end if


    is read by VBA as


    Code
    if x = y then action1
end if


    this will cause a bug out as you don't need the End If in there.... as all one line.


    so it follows that typing


    Code
    if x = y then _
action1
action2
end if


    is read by VBA as


    Code
    if x = y then action 1
action2
end if


    vba not like that.... :(


    apologies if dumbing down - but sometimes easier to say why something is failing rather than just point out the error, someone might not know exactly why one thing (like an underscore) would cause such an error - it's not overly clear.

  • Loops

    Re: Loops


    all about getting your IF THEN structure correct


    only use if x then y without end if - if you're only going to do one action... in this case you're doing multiple so you need to break it out across lines with an end if


    Code
    If ActiveCell.Value = "JPY" Then 
ActiveCell.EntireRow.Copy 
Worksheets("JPY").Select 
Rows(1 + i).Select 
ActiveSheet.Paste 
Worksheets("Badger").Select 
End If

  • Error in code to remove chart

    Re: Error in code to remove chart


    could you possibly mail me the file you're using - just the one being used for the chart ... difficult to fix this type of thing without seeing the source etc...

  • Loops

    Re: Loops


    for my money...


    I have seen few VBA courses and to be honest I doubt there effectiveness (I have no knowledge of training offered by OzGrid however so don't do me for liable).


    at the end of the day most VBA routines are specific to the problem being faced... you may find repetitive snippets of code but with the exception of "delete blank row loops" etc... most final solutions are unique and for that reason alone I don't think any amount of training is likely to get you to your solution in full ... the most important thing is just getting to grips with object orientation and which objects allow you to do what etc... also understanding the differences between standard modules you insert, the events available to you in say "ThisWorkbook" or even each sheet etc ... are the keys to a fruitful relationship with VBA! And this is not limited to XL - same for all other MS apps like Outlook, Access, Word etc etc... all have their own libraries and events etc...


    The most important of all tools for learning VBA however is the recorder... if you're unsure as to how to do something then 7 times out of 10 recording yourself doing what you want will give you an idea of the code - it is highly unlikely that it will give you the final code (and it will be messy) but it will certainly point you in the right direction for syntax etc... and tell you which objects to look at in the object browser.


    Searching here of course is another invaluable resource - as are the geeks like us who loiter around it's various forums.


    PS Spog00 - kudos if you have only been exposed to the beauty of VBA for 3 months - on that basis your knowledge (as seen dotted around in posts) is v impressive... an excellent role model !! I wish I had learnt so quickly...

  • Error in code to remove chart

    Re: Error in code to remove chart


    yeah - if it's the only chart on the sheet then you don't need to give it a caption etc.. you can literally just loop each chart object and delete it ... there should be only one so no need to do any checks.

  • Error in code to remove chart

    Re: Error in code to remove chart


    ok - well I'd suggest the best thing is to utilise a chartobject variable that is always specific to the chart you want to delete... say giving it a title?


    so if you added the following code to your add routine


    Code
    ActiveChart.ChartTitle.Caption = "ChartTitle"


    then you could use the following to delete it...


    Code
    Sub removechart()
Dim co As ChartObject
For Each co In ActiveSheet.ChartObjects
    If co.Chart.ChartTitle.Caption = "ChartTitle" Then co.Delete
Next co
End Sub


    and the code wouldn't be affected by how many times you added and removed the chart.... (obviously this works on the assumption that only one chart would exist with this title...)

  • Error in code to remove chart

    Re: Error in code to remove chart


    is the created chart embedded within a worksheet or does it exist on it's own worksheet - the chart is the worksheet (worksheet would be called something like "Chart 24").


    sorry looking at it, appears to be embedded on to Input Sheet


    is it the only chart on that sheet?

  • Error in code to remove chart

    Re: cant understand error


    I presume the error is on the remove?


    You will find that this is because the chart object will change every time you add/remove (ie will not always be chart24).


    you could loop the OLE objects to remove it ...


    anyway - can you clarify exactly where it is bugging out - hit Debug and tell use what the line in Yellow is.

  • Loops

    Re: Loops


    the reason is the positioning of your cell activation and offset....


    if you did i = 0 to ...
    then you would use

    Code
    ActiveSheet.Range("B2").Select
'B2 now active cell
ActiveCell.Offset(i,0)
'make active cell i rows down from B2 - so first time i = 0 so active cell remains B2 ... then when i = 1 active cell becomes B3 etc...
'then do your Yen check and bold etc
'remove the active cell offset line prior to next i
Next i


    currently the way you're running the code - B2 is ALWAYS the cell that is checked as you activate B2 ... thus it is the active cell whenever you loop...

  • Loops

    Re: Loops


    Code
    Dim i As Integer
Dim Finish As Integer
Finish = 16
For i = 2 To Finish
    If Cells(i, 2) = "JPY" Then Cells(i, 2).Font.Bold = True
Next i

  • Compare Data for exclusion

    Re: Compare Data for exclusion


    depends on how you want to add the new addresses... you could use an InputBox Prompt to enter the address and then check it... or if you prefer to just put address in a cell and check - say address entered in A100


    formula B100 = if(iserror(match($A100,$A$1:$A99,0)),"","DUPE")


    you can copy that formula for all rows required without changing it.