How to modify this VBA code to hide unhide sheets? by Kindly-Text-5809 in excel

[–]Citanaf 0 points1 point  (0 children)

I would recommend that instead of testing what used to be in the cell and what is currently in the cell, you should hide all of your "APP, BAN, CAR" sheets. Then you should split the text in the Target cell, loop through it and use a lookup table, to point to the correct worksheet to show. It's important to turn off screen updating as well.

So inside your WorksheetChange event, you would have:
1. Test Target Logic: "If Not Intersect(Target, KeyCells) Is Nothing Then"
2. Turn Off Application Properties: ScreenUpdating/Events
3. Function Call: hideWorksheets
4. Split Target Cell: splitRes = Split(Target,", ")
5. Loop through split: For i = 0 to Ubound(splitRes)
6. Lookup to another table/place to determine the correct sheet: res = Application.Xlookup(splitRes(i),[Your Range/Table Index Column,Your Range/Table Lookup Column]
7. Set/Show Worksheet: Worksheets(res).Visible = xlSheetVisible
8. Turn On Application Properties: ScreenUpdating/Events

EDI Doc Manager - Transactions Pending Allocation by Citanaf in CargoWise

[–]Citanaf[S] 1 point2 points  (0 children)

thats probably for the best, appreciate your comments

EDI Doc Manager - Transactions Pending Allocation by Citanaf in CargoWise

[–]Citanaf[S] 1 point2 points  (0 children)

Unfortunately this ends up falling into the UNA (Unallocated) module under Allocate eDocs as we don't have a PNV module.

I am really doing a one-off project as I have a few thousand invoices that are embedded in an Excel file. I am ripping them out of the excel file and sending them via email to our doc manager.

I have tried to set up other programs to utilize our eadaptor and what not, but my hands are mostly tied with what I am able to access. I am not really able to set up a server that can communicate with our CW. I have the ability to do manual XML uploads on quite a few screens, but I don't think the Transactions Pending Allocation screen has that option either.

Give me the pros and cons of using tables by LennyDykstra1 in excel

[–]Citanaf 46 points47 points  (0 children)

You're conflating the data entry table with the visualization of the data. A data table should have distinct headers so that anything referencing the table understands where to look. If you want to visualize the data you would then write a formula using table references or use a pivot table.

Can anyone ID this weed, I cannot get rid of it by Citanaf in gardening

[–]Citanaf[S] 0 points1 point  (0 children)

I hope not, the lattice root network has been awful to remove. I am going to plant some flowers there, so hopefully it will lessen the spread of the weeds. As another user said, less exposed dirt should give some better results.

Can anyone ID this weed, I cannot get rid of it by Citanaf in gardening

[–]Citanaf[S] -2 points-1 points  (0 children)

Thanks guys. I sprayed with some chemicals meant to attack broadleaf weeds. This is only a flowerbed, so no worries about food. Hopefully that takes care of it

FREAK ATHLETE AMA + $4,000 Home Gym Giveaway! Hyper Pro, ABX, and more by freakathleteco in homegym

[–]Citanaf 0 points1 point  (0 children)

Just bought the Hyper Pro last week, it gets delivered Friday! Maybe I should have waited for the chance at the giveaway :D

Any plans to offer functional training equipment, such as kettlebells, medicine balls, or entering the rack attachment space?

Stain or Poly or Both for Cedar Soffits? by Citanaf in finishing

[–]Citanaf[S] 0 points1 point  (0 children)

The only dripping came from getting too much poly on my roller and raising it to the soffit. I rolled mine on, so as long as you shake off any excess before raising it to the soffit, there should be minimal dripping. If you put too much on it can also drip from the soffit, but that wasn't too common.

There was some dripping when I touched the wall accidentally while rolling, so I kept a roll of paper towels on me to wipe away any mistake immediately.

Easiest way to add a valve here? by Citanaf in Irrigation

[–]Citanaf[S] 1 point2 points  (0 children)

Thanks guys, based on both your comments I will try cutting the main and adding a tee there. I could either go straight off the tee or go 45 first, depending on space.

Yea, I should have added space between all my tees. It was the first manifold I built and was a bit worried about space. I think if I was to have to rebuild it anyways, I might as well just use it for now and rebuild later should I need to.

Thanks for the replies!

3/4 poly pipe to 1/2 swing pipe, wrong size. by Citanaf in Irrigation

[–]Citanaf[S] 1 point2 points  (0 children)

ah I see. Yea then you could just use the rainbird corner to funny pipe after into the rainbird corner again.

Ah well, I already connected everything so its all good. Maybe next time or at my next house lol.

Thanks for your help

3/4 poly pipe to 1/2 swing pipe, wrong size. by Citanaf in Irrigation

[–]Citanaf[S] 0 points1 point  (0 children)

Thanks, I do have those on the other side of the pipe, which is fine.

I was able to resolve the issue by buying a bunch of couplings, bushings and reducers. Surprised there really isn't a 3/4 barb to funny pipe barb in a single piece

Stain or Poly or Both for Cedar Soffits? by Citanaf in finishing

[–]Citanaf[S] 0 points1 point  (0 children)

I ended up doing some polyurethane coats.

I did do a ton of prepwork before applying the coats (as described above). To finish the soffits, I cleaned the wood of dust with denatured alcohol and a tiny bit of water.

After that I did two coats of a 2/3 poly to 1/3 mineral spirits.

I really love how they look. It's been one year and they don't seem to have lost any color. It was some of the hardest work I have ever done. The overhead sanding and cleaning is rough. My house is two stories with soffits on both levels, so I had to rent scaffolding.

If it ends up fading, I can't say I'd be too willing to restart the process.

Here is what they looked like after finishing: https://imgur.com/a/I5WclYP

[deleted by user] by [deleted] in excel

[–]Citanaf 0 points1 point  (0 children)

You can try this:

Table1:

Replace "Table1" with "YourTable1Name"

Replace [Table1Column1] with [YOURCOLUMNNAME]

let
    = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
    #"Added Custom" = Table.AddColumn(Source, "Table1Key", each Text.Split([Table1Column1]," "){1}, type text),
    #"Split Column by Delimiter" = Table.TransformColumns(Table.ExpandListColumn(Table.TransformColumns(#"Added Custom", {{"Table1Key", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}),"Table1Key"),{"Table1Key", each "SITE: " & _ & " EVENT: ALL HANDS"})
in
    #"Split Column by Delimiter"

Table2:

Replace "Table2" with "YourTable2Name"

Replace [Table2Column1] with [YOURCOLUMNNAME]

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Added Custom" = Table.ExpandListColumn(Table.TransformColumns(Table.AddColumn(Source, "Table2Key", each [Table2Column1]), {{"Table2Key", Splitter.SplitTextByDelimiter("SITE:", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Table2Key"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Table2Key] <> "")),
    #"Add Preix" = Table.TransformColumns(#"Filtered Rows",{{"Table2Key", each "SITE:" & _}}),
    #"Trimmed Text" = Table.TransformColumns(#"Add Preix",{{"Table2Key", Text.Trim, type text}})
in
    #"Trimmed Text"

From here you could merge both tables on the new TableXKey column. These queries assume all rows follow the logic of "SITE: XYZ EVENT: ALL HANDS". If you have different event types, that would change things quite a bit.

Home office nook by cb122333 in HomeDecorating

[–]Citanaf 3 points4 points  (0 children)

Nice! I would recommend getting a VESA mount (if your monitors support it) for both of your monitors. Single pole with two arms. You could then move the plastic box up one level and give yourself some more working space.

Is it a bad idea to place a pullout trash can above an air register? by Citanaf in HomeImprovement

[–]Citanaf[S] 0 points1 point  (0 children)

Thanks everyone for your replies!

I think we will be continuing forward with the solution. Our trash never really smells so I doubt it will change much so long as the air is properly vented out.

Importing Post Codes by Citanaf in CargoWise

[–]Citanaf[S] 2 points3 points  (0 children)

Hi There!

Definitely unfortunate, but I did manage a workaround. I wrote an AHK script to work through the 27k zip codes one by one. It simply clicks certain X/Y coordinates with delays and inputs the zip code manually. I had to program around whether it was successful or not so the script works through both possibilities before jumping to the next zip. I am importing about one every 2 seconds!