Posts by clairexcel

Hi Kjbox, yeah I noticed the error this afternoon and I corrected it.

Afterwards it gave me error only with the items codes that initiated with a Letter e.g Y1245875.

The others e.g 2534789, it would do them correctly.

However thank you for the time and help, it is much appreciated. You helped me greatly.

thank you

KjBox hi, I am so sorry to trouble you again, but at work with real code items this code gives me this error:

Next

If x(i, 1) = x(i + 1, 1) Then

The error I get in the message of Debug is this one:

While on the table with fictional data works wonderfully and fast, on the table I have at work, it doesn't.

I thought the real format of the item's id (in col. A ) was to blame, because otherwise I ca't explain to myself why the code works perfectly in the sample table of yesterday, and in this one it doesn't.

I attach a sample of the table I have at work, with the item's code in the format as it is (I of course changed didgits and letters)

Can you please see why it keeps giving me this error? Thank you in advance...

Quote from KjBox

You're welcome.

The reason for the error is that your Table allows for only up to 4 duplicates, add 3 columns to you table, "price-5", "from qty5" & "to qty5".

Also the formatting for "price-5" needs to be added to the code

Amended code

Display More

KjBox thank you!! I added the formatting in the code for price -5, but I mess up the For ii = 1 To 4 part.

It is working, and I cannot express enough appreciation for your time and help.

Thank you!

Kjbox, what can I say besides THANK YOU? You have saved my work once again.... SO very much aprecciated.

The code works wonderfully and very fast.

The only problem is I made a trial with the table adding 4 rows of dupplicates for X17, but it gives me an error, this is the line that Debug highlights:

y(vi, ii + v) = x(i, ii)

I tried to increase the number for : For ii = 2 To 5

But it still gives the error.

Why is that? I may have up to five dupplicates for a price.

Thank you really Kjbox, this is fine quality code and for the n-th time you have saved my hours at work!

Quote from KjBox

If all you "Price List" files are in a single folder, and no other files in that folder, then it would be possible write code to loop through all the files and reformat the Table as required.

No , unfortunately are not in the same folder, every customer/supplier has its own folder, it is a company requirement.

But the idea is of couse brilliant..

Quote from KjBox

No file attached

Oh sorry for the mishap, here it is.

Hi there,

I have a bit of a conundrum: multiple files (price lists) with an average of 15000 rows.

These price lists have in column A the item's ID, in column B the price and in column C the quantity for that particular price. Now, some items are duplicated from 2 to even 5 times, as prices for these items are qty based/scaled.

Problem is I have to have these files with scaled prices per column and not per rows.

WHat I have been trying to do (mostly with formulas, whithout success) is: Find the duplicates in column A, and If dupplicate found paste the range B:D on the same row of the first ID matching, as many times as matches found. I'll show you in the example below. This is the starting file

What I need to do (preferably VBA as there are files that have up to 25000 rows), is obtain this, please see what I did with items X8 and X 17:

I attach the file I have been working with. PLease ignore the modules I have in it, they are codes I found online and have been trying to workarround work arround those, as well as some formulas but I haven't been able to obtain what I needed.

Anyhow, thank you in advance for any suggestion or help.

KjBox you are just so awsome !!!

I knew it it was this I had to write (from the code you have given me last year with the machines sheets):

z(ii - 1, 1) = y(i, 1): z(ii - 1, 2) = y(i, 2): z(ii - 1, 3) = y(i, 3)

But I wrote it wrong, to say the least :)))

The only thing I got right is ReDim z(1 To UBound(x, 1) - 1, 1 To 3)   this line I modified correctly.

Thank you so very much for the code and also for the explanation, I really will study the lines and your explanations.

Thank you from the heart!

KjBox , If you wish of course, could you explain to me (just me learning and trying to understand) two things please :

1. If the 13 chars match I want the code to copy also columns B and C from sheet 2. I am currently trying to figure the code in order to tell it " if match found copy also B and C columns from sheet 2 to sheet one, starting colum D.

I guess I have to give another variable, besides z, and then add a Foglio1.Cells(2, 5).Resize(UBound(v, 2)) = v kind of thing? Or change the Redim of z?

2. Match(Mid(y(i, 1), 2, 13), x, 0) - how does this function reasonate?

I mean, I get the 13 part :)) because it refers to the 13 chars the cells have in common. But why the 2? and the X,0? How does the code understand that it has to ignore the first letter of the string?

Thank you in advance for any explanation.

It's just that your codes are very complex, fast and I want to not only apply them but also to learn.

thanks!

KjBox and alansidman thank you so very much.

I wanted to give you likes but I can't find the Like icon anymore, whay is that? Edit: found the like icons :))

Tried them both, the faster is KjBox's code, I mean it literally finds any match in no time... and there are many many rows.

I will study them both anyway, and learn.

I really appreciate your help a BIG thank you!!!

Hi KjBox ,

I mean when a match in the string of 13 chars is found I would need the macro to copy the cell A from Sheet 2 into Sheet1 in the corresponding row of the corresponding Main Code. I attach again an example of how I need it to be done. I highlighted two matching codes, and copied from sheet 2 the corresponding row, pasted it in Sheet 1 starting in cell D (which is empty)

Thank you for your help!COMPARE TWO COLUMNS FOR STRING MATCH BUT ONLY 13 CHARACTERS AFTER THE FIRST ONE-2.xlsx

Hello everyone,

I have been trying to come up with a solution on my own for this, but unfortunately I am stuck.

I have a file with two sheets, with item codes and relative info and prices.

On sheet one I have the main code of the items in column A. On sheet 2 in column A I have the secondary code (the component of the main item code)

Item codes (the main and the secondary) are similar in the middle, they differ in the first character and in the last/last two. They will have the 13 characters (after the initial one, identical) E.G:

• Main Item code is X1234567891123GT
• Secondary code for the item above will be: R1234567891123YQ

I have a file with many thousands rows So I need a macro, to look in Sheet 2 Column A for a match (in the 13 characters after the first character) to item codes in Colum A SHeet 1and if match found it should copy the row starting cell D sheet 1 (so adjacent to the main item code matching).

Any input, suggestion or help is very appreciated.

thank you in advance.

Quote from KjBox

Change that line of the code to

Code

If lRow > 7 Then .Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2

Hi KjBox, thank you s so much. I did replace the line and now it does not give me error 1004 anymore, BUT it overwirtes the existing lines in my machne sheet, and that is a problem...

How can I make the code to work as previously, no matter how many items I added during the day from the sheet SPARTISCI, it added the new items in the machine sheet bellow the last used row. It has to be this way otherwise I loose the preexisting planning on the given machine..

Can you please help with this too? Thank you from the heart

Hello everyone, hello KjBox

KjBox can you please help me with an error in the vba code that you so very kindly provided me with? It is the vba code that splits rows into the machines, based on the column that contains that number of the machine: it splits the rows from the sheet SPARTISCI containing that machine number in column B ,into the machine sheets; and they are non contiguous columns so you wrote a wonderful code taht worked perfectly untill yesterday.

I do not know why.

What I tried to do : two weeks ago, I added in the vba code another column form X sheet (the source sheet) to go into the machine sheets and it still worked like a charm.

Today, when I had to update the machines after a few days that we weren't working in the company, it gives me an error and highlights this line:

.Cells(8, 1).Resize(lRow - 7, 13).Borders.Weight = 2

Now, why did it start giving error if it worked fine untill yesterday?

The only thing I added is two new sheets in the same workbook wich however have nothing to do with the machines.

Funny thing is for some machines it works fine even if it gives me the error: the code puts the rows in some of the machines, gives the error and does not compile other machines.

Please help me...I have to plann 106 orders ahead tomorrow....I am at lost without your help. I am desperate...this code helped me do my job in the last month in a way I couldn't hoped for otherwise.

Thank you so so much, I attach the file as it is. Thank you relly in advance

Thank you Kjbox for the detailed explanation it helps me very much to understand to reasoning of VBA in this specific scenario and thus learn some more.

I have been googling a lot since yesterday about variables, multiple loops and about lOff. The latter, as I understood your code is the one variable that tells the code what cells to kip....I got this correctly?

Anyhow, everytime I feel proud of myself and satisfied that I learned something new in vba and I built a code or a piece of code entirely by myself, I get here, I see your codes (of all you experts), and how you reduce a code to few clear concise lines, how you use the variables and the arrays, and how many ways there are to do that...and I feel less proud :)); as I understand I still have to learn so much;

You are a great source of learning, all this forum!

KjBox, it is just perfect. I now have to study the conditions you gave, I had no Idea that vba could "understand" what weekday is by just reading the dates...incredible.

I really can't express enough apreciation for all your help. Thank you from the heart.

Whenever you can kjbox, I appreciate very much and thank you!

My difficulty is that I have to give to the code a condition for the cells in a the range that consists of the c.offset...

I am to ask another question/ask for yuor help on this code again.

I have been trying to find the line of code myself without success.

I want to add a ondition to the above code, as follows, for each c in range, loop and color each adiacent cell with the value of the cell in range B, BUT only if cell does not contain text. If ANY of the adiacent cells contains text, then skip those cells. Of ocurse the number of cell the code colors must be equal to the value of the cell in column B..

I want to add this condition because the machine graphic as it is with thIs code is not realistic: it has to skip saturdays and sundays. So in the cells that fall in weekends I will put an X , so the code should skip those and color only the empty cells. How do I indicate this condition in my code?

Sub machinesrgraphic()

        Dim c As Range
        Dim ws As Worksheet
        Set ws = Sheets("CARICO MACCHINE")
     

Application.ScreenUpdating = False

On Error Resume Next
For Each c In ws.Range("B6", ws.Range("B" & ws.Rows.Count).End(xlUp))
c.Offset(, 1).Resize(1, CInt(c)).Interior.ColorIndex = 33
Next c
             
        
Application.ScreenUpdating = True

End Sub

I attach a sample of my file, thank you in advance for any suggestion.

Hi kjbox and thank you for your help, again! Works like a charm.

Brilliant, really THANK YOU.