Link to home
Start Free TrialLog in
Avatar of Weldon Schultz
Weldon SchultzFlag for United States of America

asked on

Macro or command to round up to 250 using CEILING function.

Test_CEILING-250.CSVAtlas.xls


I have a situation where a company has changed how their software calculates product and it has messed our system up. We used to have it set in our account (on their system) that we could calculate screws to always round up to 250. That no longer works and doesn't sound like they will go back to that, or if they do, it might take a while to implement it. We really need to have a number that is divisible by 250 as we sell screws by the bag, even though they are priced in our database as Each. 


So, if a project needs 2,117 screws, the spreadsheet needs to figure 2,250. Is there a way I can make a macro or VBA on a command button that anytime I need to, it can go through the spreadsheet and find any of the screw codes and run the CEILING function? 


I have attached two spreadsheets, one has the list of screw codes, the other is a sample .csv that has 2 different sized screws and some other material. That .csv is exported from their software, I need it to round up to 250, then we import that .csv into our databse. 

Avatar of Martin Liss
Martin Liss
Flag of United States of America image

=CEILING(A1,250)
I just noticed that your csv file contains these two entries (the bolding is mine). Were you aware of that?

15AWH,"Atlas 1.5"" Wood Screw Type 17",0,0,4218,,4250
2AWH,"Atlas 2"" Wood Screw Type 17",0,0,465,,500
Avatar of Weldon Schultz

ASKER

Yes, I was aware of that. Those are the numbers I want. I know how to calculate that, what I'm needing is some way that I can have a formula check for the case of all the screw codes in Atlas.xlsx, if the screw code in the .csv file matches any of those codes, then it runs the CEILING function and rounds that number up to 250.

The Atlas.xlsx file is not exported every time, only the .csv. Actually, the more I think about it, it might be far simpler to have a formula that checks for the word "Atlas" in the Product column instead of trying to work with a file or codes that are not included in the .csv.
Test this sample (press RunMe, open source file, file with added _o to file name will be created in the same folder)

Atlas.xlsm
@als315, that works awesome! I had to use chat.openai.com to help me out a little as I had never worked with that exact VBA code, but we got if figured out. Thanks a million!

Now then, is there a way in the same .xlsm to find the rows that have descriptions in, move those cells over to the next cell to the right and insert the letters com in the cell just emptied?


MoveCell_FillVacatedCell.png
All is possible :)
Please, prepare sample file and show expected result
I think we're getting closer, but I'm getting an error that I haven't been able to figure out yet. Also, if I'm understanding that code right, it is just concatenating "Com" in front of the descriptor, not moving the cell contents to the right and then filling Cell A with "Com". Please see the attached .mp4 to show what I'm needing.

(I don't need the cells to be filled with color like in the .mp4, I just did that to highlight what I am needing in those cells. Our database recognizes that as a Comment instead of a Product.)

User generated image
User generated image2023-03-16-07-28-08.mp4
I'm working with csv file - text file with delimiter comma.
Excel only show csv file as worksheet. We can move and add cells in Excel, but in this case we can do it in text file.
Line in file:
Framing,,,,,

Open in new window

should be changed to:
Com,Framing,,,,

Open in new window

"Com" and comma is added to the beginning of line and one comma is removed at the end of line. You can do it in any text editor and result will be the same.

An error may be the result of empty lines in text file. I've added code to skip empty lines.

Atlas_v2.xlsm
I hope today is your 'Have Plenty of Patience Day'. I have tried and tried to make this work and I keep getting errors.


2023-03-16-14-50-04.mp4
Test-screws.txt
Atlas_v2.xlsm
New csv file has new structure. It is important.
new file:
Framing
old file:
Framing,,,,,

I've adapted code to both variants
Atlas_v3.xlsm
The only thing that I can see that isn't perfect is the first category has some symbols appended to the front.

Com      ï»¿Framing
ASKER CERTIFIED SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial