Posts by stildawn

  • Line Graph - Connect dots but as data is entered

    Good Day

    I'm having an issue with this line graph attached.

    Basically I am keeping a running daily total year on year, I want the line graph to connect the lines for missing dates inbetween data but as you can see below, this new year has a giant line (orange) across the graph even though data isnt entered that far out as yet.

    Any ideas on how to fix? I just have dummy info in 2021 currently but eventually in April this year there will be two lines that are close and I can see visually the difference from last year.

    Thanks

  • Costs Database - Accessible by numerous staff at various sites

    Not saying it would need VBA in Access but it might.


    In excel it would in my opinion.


    A shared workbook would be a nightmare I think, I have done them in the past and always horrible experience, plus the majority of users dont need to do anything other than bring up the costs they need. Data entry etc would be handled separately which is why I was thinking of a Access backend / frontend setup.


    Does Access still have the free frontend version? As I'm fairly sure the company does not have Access for all users.




    Access is going to be difficult for me haha, I haven't really done anything on it for years now and the user form to reference the data will need to be fairly complex in my mind, like the POL / POD names would need to appear as a user types them for example, as there will be literally hundreds and hundreds of port names.

  • Costs Database - Accessible by numerous staff at various sites

    Hi All


    I am about to start a large ish (hopefully) vba project, and just after some more experienced advice/thoughts on it before I invest too much time.


    My company (multinational) has a need for a database of freight costs, each freight cost will have 7 fields (maybe more):


    POL (port name) / POD (port name) / Carrier (short string) / Start (Date) / Valid To (Date) / Type (a few set options) / Cost (number) ------- And maybe a few other costs types as well depending on what is needed.


    These costs then need to be presented to each user based on what they enter for POL / POD / Carrier / Type etc.



    Now I know I can do this in excel, have a sheet with all the rates as each row (hundreds of rows) and then a user form to present to the user, but I was thinking access might be better, having the rows as a backend database, and then a simple front end user form referencing that backend data.


    I'm just after what you very talented and knowledgable individuals would do in this case before I invest too much time into it?


    I am much much better at Excel vba than Access, but I have made smaller access programs in the past, although I'm worried about the backend being on a server shared drive and that causing issues.



    Or is there a whole other alternative I could look into beyond Excel/Access, one that has a short learning curve as I would have to learn how to do this myself.


    The backend/master freight costs data would need to be easily updated, excel/access is easy to do this via a template file etc but that needs to be considered.


    Thanks in advance :)

  • Simple Row Cut & Paste Not Working For Some Reason

    Re: Simple Row Cut & Paste Not Working For Some Reason


    This part:


    (wsN.Cells(wsN.Rows.Count, 1).End(xlUp).Row + 1, 1)


    The original wasn't finding the new lastrow on the correct sheet.





    Good to know about the cut command, I'll be sure to remember that in future. I usually copy and then delete as well but thought why not just have one line instead of two.

  • Simple Row Cut & Paste Not Working For Some Reason

    Re: Simple Row Cut & Paste Not Working For Some Reason


    I fixed a referencing bit in your code to the below and it worked:



    Don't know why yours worked and not mine though.


    Thanks though :)

  • Simple Row Cut & Paste Not Working For Some Reason

    Hi Al


    I'm sure I have used this code numerous times in the past but for some reason its not working.


    I'm getting a 1004 error on this code:



    It doesn't matter how I rework it, I still get errors, for example if I do a simple "paste" then I get 438 errors:



    If I change the Rows to a range and the Range to a rows still get the errors.


    Any ideas, this should work.


    Thanks

  • VBA code not working when using UNC path for saving file

    Re: VBA code not working when using UNC path for saving file


    Thanks S O looks intense.


    Is there anyway to test this? I unmapped one of my network drives and then tried to remap and disconnect using the code while watching my computer haha, but nothing seemed to happen?


    Is there also a way to check for already mapped letters so I can build that in?

  • VBA code not working when using UNC path for saving file

    Re: SaveAs Failed - I think because of network UNC


    Here is the code instead of the picture (sorry that was the picture sent by the user):




    Quote from S O;779068

    I'm pretty sure the MkDir command doesn't support UNC paths - but you won't see that in your code because of "On Error Resume Next"


    If it can't create the folder, then your save path is invalid.


    One option could be to actually map the drive within the code, and then disconnect it when you've finished.


    Ahhhh that could make sense, so the real error could be that it can't make a directory on a UNC path.


    Is there another make directory command that would work with UNC?


    Or alternatively, can you advise how I would map the drive within the code, never done anything like that before. Would mapping/disconnecting cause any issues with their other windows experience? Like I assume they use the network drive for other work etc and I don't want code that would mess up their other experiences in using the network drive.

  • VBA code not working when using UNC path for saving file

    Hi All


    I have a tool I've been developing, and basically when you run it for the first time, it asks you to point to a save location (which it then uses in future when making different files and saving different things).


    My end user is getting an error on one of the save as commands. Here is the issue step by step:


    Full path saved for useis: \\nzsrv01\companynz\CompanyShared\CLIENT RATES & INFORMATION\I\ICEBREAKER\CT BOOKINGS\2_SPO\SPO Masters\11.10_GHF


    When the user runs the code this happens:
    [ATTACH=CONFIG]70407[/ATTACH]


    Debugging to this line of code:
    [ATTACH=CONFIG]70408[/ATTACH]


    You see its just a save as command. Now this works perfectly if the installpath (which is the full unc path listed above in this case) is a normal like "C:\Blah" path. But it seems to be throwing this error due to the unc network path, even though as far as I know Excel vba should accept these paths.


    Any ideas/tests I can do to figure this out.


    I know I could map the network drive, but eventually multiple users will be using this so would rather avoid having the users do any mapping etc.


    Thanks

  • Comparing two sets of data (ranges, rows, columns whatever) and find changes

    Re: Comparing two sets of data (ranges, rows, columns whatever) and find changes


    There is other data that doesn't change (automatically) in other columns that needs to be manually updated based on the changing data that my code handles.


    So no replacing old with new is not what I need.


    Code/process I need is like this random example:


    OLD DATA
    [TABLE="width: 500"]

    [tr]


    [td]

    QTY

    [/td]


    [td]

    Delivery Date

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    22/09/16

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    21/09/16

    [/td]


    [/tr]


    [/TABLE]


    NEW DATA
    [TABLE="width: 500"]

    [tr]


    [td]

    QTY

    [/td]


    [td]

    Delivery Date

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    22/09/16

    [/td]


    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    24/09/16

    [/td]


    [/tr]


    [/TABLE]


    In these lame examples (there is both more columns to compare and many many more rows), I need code that will compare the data in "NEW DATA" to the data in "OLD DATA"... In these examples it would highlight both rows in "NEW DATA" cause both rows have changed, the QTY on row 2, and the date on row 3.


    If the data was identical, it wouldn't highlight.


    Does that make sense.

  • Conditional Formatting - Changing to #REF!

    Re: Conditional Formatting - Changing to #REF!


    Basically here is the sequence of events.


    Master sheet has a bunch of data on it, including conditional formatting (as above).


    > This master sheet is saved as a xlsx (to remove code) the person A sents it to person B, Person B enter in data into column L. Column L then highlights etc due to the conditional formatting rules.
    >> Person B save this (with the added data) and send back to person A
    >>> Person A runs the code to import the data that Person B provided (in the xlsx file) into the master file (xlsm with the coding). The code dumps all the data from the xlsx, into the "Import" sheet of the xlsm file. It then runs the code above to copy across the relevant data from "import" to "master" sheet, both the import sheet and master sheet have the same conditional formatting rules on them, however when the code copies across the data from "Import" to "Master", the conditional formatting rules on the "master" sheet now shows the #REF! errors...


    I think your right though in that when I delete row 9 in the "Import" sheet, it destroys the conditional formatting on the "Import" sheet, and then when it copies across to "Master", it also copies the now destroyed conditional formatting from "Import" to "Master"


    I'll redo the code so that it doesn't delete rows 1 - 17 on the "Import" sheet and see if that changes it.

  • Comparing two sets of data (ranges, rows, columns whatever) and find changes

    Hi All


    I have a sheet (old version), it has say 4 columns of data on multiple rows (can be thousands).


    On another sheet, I have the same data (new version), however some rows might have changes...


    I need vba code to compare the new data with the old data. If there is a change on a row ideally I want to highlight that row a color (grey for example but I can mess with this later).



    The idea behind it is that a person will have to update the rows, and I want to highlight out the rows that have changed so they don't have to actually update every row every time, but just the ones that have changed.


    I know I can do this with an insane loop, but again I'd like to avoid loops if possible due to the data size and processing time it would take.

  • Conditional Formatting - Changing to #REF!

    Re: Conditional Formatting - Changing to #REF!


    It deletes rows 1 to 17 on the import sheet, but row 9 still very much exists on the master sheet which is where the conditional formatting is.


    Unless your saying that when it copies across the data from "import" sheet to "master" sheet it copies the conditional formatting also?

  • Conditional Formatting - Changing to #REF!

    Hi All


    I have a master sheet, which has some conditional formatting in L18 down. The formula is this: =L18>$D$9+7


    This is set and working fine.


    In the process, we load in some data from another workbook, and put it into the master sheet with this code:


    Code
    '##### Clears out the unwanted data on import sheet & copies across ####
With ThisWorkbook.Sheets("Import")
    .Rows("1:17").Delete
    
    Lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("L1:N" & Lastrow).Copy ThisWorkbook.Sheets("Master").Range("L18")
End With
'#######################################################################


    As soon as this part of the code runs however, it somehow messes up the conditional formatting on L18 down in a weird way...


    Turning it from the correct formula: =L18>$D$9+7
    To: =L18>#REF!+7


    I don't understand why/how its losing the "$D$9"?


    There is another conditional formatting formula that is slightly different but it does the same thing, it loses the "$D$9" replacing it with the not working "#REF!"


    Any idea what is going on, and how do I fix it?

  • Pipe "|" Delimiter CSV?

    Re: Pipe "|" Delimiter CSV?



    I just coded it to do it within excel, but the loop is fairly slow, takes a minute or two on big files, will try your print to file way as well and see which is quicker.

  • Pipe "|" Delimiter CSV?

    Re: Pipe "|" Delimiter CSV?


    Quote

    No problem, you just name the file with any extension you want (Even .XLSM). when writing to a file, the extension you use doesn't really matter as long as the program reading the file can understand the structure.


    So would I not need to create this "csv" file first? Would simply having it as the "open "blah.csv" For output as #1" work?


    The "~" is already in the last column of the work sheet, so I believe your code will automatically include it correctly.


    Could you explain why doing it within excel (into A1) would be a pain? Wouldn't it just be something like:


    Code
    For j = 1 to UsedColumns - 1
.Range("A1").Value = .Range("A1").Value & .Cells(i,j).Text & "|"
Next j


    Wouldn't that work? Or is there something I'm missing.

  • Pipe "|" Delimiter CSV?

    Re: Pipe "|" Delimiter CSV?




    Thanks Cytop, this looks the best solution.


    Can you run me through the code so I can best implement it into my code:


    Code
    '// Define a suitable file name
   Open "C:\temp\pipedexport.txt" For Output As #1


    They need it in a ".csv" file, can I edit this file name code to generate dynamically something like:


    Code
    Open ScoutSavePathStr & "\Scout Upload " & RefNoStr & " - " & CurrentDateStr & ".csv" For Output As #1


    Or do I need to actually generate this file before the above? Basically currently I have all the data in a sheet called "Scout" (which is the system its uploading to), need to get all the data in that sheet into a piped CSV file.


    Code
    For i = 1 To UsedRows
         For j = 1 To UsedColumns - 1
            Print #1, .Cells(i, j); "|";
         Next j
         Print #1, .Cells(i, UsedColumns)
      Next i
   End With


    From what I understand, this loop is simply going through all used cells in the sheet and printing the contents + "|" into the text file (the output file). Will this work as is with a csv file?



    EDIT: Just re-reading the instructions from the Scout people (who are making this a pain in the ass lol), here's there vague instructions:


    Quote

    1. Fill in the data in one cell, use | as delimiter and remember to enter ~ as Line End Indicator of each line.
    2. Save the file as Comma delimited CSV
    3. Send email to "[email protected]"domain.com with the CSV as attachment, Subject: Reference


    Couldn't the above code work straight in excel without the print to output? Just do the above in A1?


    Thanks :)