Hi Carim
The code works perfectly so a huge thank-you, as always. Scott told me about your reply. We have been doing this project for a long time. Scott does one part or has an idea and I do other parts. VBA is new to both of us so it’s been quite the leaning curve on how we can use VBA do things differently.
I know I’m always very grateful for your code and hope you’re not mad at either of us.
Thank-you again
Angela
Thanks so much, I really appreciate it. I will work on it this weekend.
Angela
Hi
1) Yes, a new data block has to inserted.
2) Yes, the cell reference used to search for matches ( ' 1-1 ' ) is no longer located as the very first cell of the range ... but in the column to the right ...
3) Yes again to In addition, is there a third change : wondering if the second reference cell ( ' 5.45 ') is also to be placed elsewhere... i.e. one column to the left .... ???
Thank-you so much
Hi
I’'ve changed the clear contents issue, great. Uh, I was wondering if you have any time to inset a new column (column D below) in the code for me. The green highlighted area is the result of running the code. The code is very extensive and I do try to understand it but I don’t always understand the spacing or column references it uses to perform it’s task. If you don’t have time I would understand and I really appreciate the code.
Angela
[ATTACH=JSON]{"alt":"Click image for larger version Name:\tVERSION 5.png Views:\t1 Size:\t64.6 KB ID:\t1216766","data-align":"none","data-attachmentid":"1216766","data-size":"full","title":"VERSION 5.png"}[/ATTACH]
Hi Carim
It was me, of course. It works but I need to make a small change if that'’s okay. To start, using your version 3 Excel sheet, see image below, the range Set rng1 = Range("C9:G16") and Set rng2 = Range("C18:G25") is what the data would look like prior to running the macro. I’ve inserted, in green highlight, the expected result after the running the macro. The data comes from cells P27/Q27/R27 and for the second range cells P19/Q19/R19 .
In the code there is a header with “Clean Output” and then “clears contents” that is not needed by me (I’m just asking, not sure what it’s clearing or if it’s required by the code) but my cells don’t need to be cleared before running the code or after.
As always a massive thank-for for your patience and experience. Your the best.
[ATTACH=JSON]{"alt":"Click image for larger version Name:\tVERSION 3.png Views:\t1 Size:\t73.6 KB ID:\t1216738","data-align":"none","data-attachmentid":"1216738","data-size":"full","title":"VERSION 3.png"}[/ATTACH]
Hi Carim
Sorry for the delay in getting back to you but I’m having a debugging problem and I always have to triple check to make sure it’s not something I doing wrong. This is really impressive code. It’s highlighting the following line:
last1 = ActiveSheet.Cells(Application.Rows.Count, ColLette
I really hope it's not me causing the problem.
"Making changes to data is never a problem ..." - the data is not the issues it's which cell or cell range to find, divide etc and what cells to write the data to is the issue. That's why using a row can't work because I may have data/formulas (that can be changed with some code so that's a big problem, has happened to me).
"describe the changes you are making" Using my sample data Excel sheet would be moving cells from H3:H10, I3:10,J3:J10,K3:K10 toL20:l27, M20:M27,N20:N27,O20:O:27 with data in cells right up to the top of the sheet.
Hi
I understand where you are coming from but even if we get it to work I keep making changes to my sheet and so next week or next year then it won't work again. That's why I have to have specific cells for all my code so it can be changed as I make changes.
Hi Carim
I got your code and this looks really like a lot of work which I greatly appreciate. It definitely works in my sample workbook but not so great in my day-to-day Excel sheet. In your code you’re using column references which I can’t do because I have data, formulas and other code running in theses columns so as a result your code doesn’t work properly.
That’s a lot of work in your code. Is it possible to change all references to specific cells? I have to be able to change the cell locations to use any code in my workbook. If its way too much work I would understand if you can’t help on this one.
It’s not always easy to write this stuff up. Using my sample image the main thing to accomplish is to match cell A3 (1-1), C3 (5.45) with matching 1-1 in cell H12 and the 5.45 in J12 and then the matching line A4 with H13. Maybe you have another way to get there.
Thanks again for your help.
Karen
Thank-you for your reply
I will attach my Excel workbook. The first expected write result for the found set of number 7-1 (located in cell B4 is cells C4/D4/E4). The second expected write result for the found set of number 7-8 (located in cell B16 is cells C16/D16/E16). Please let me know if I can add more details.
Thanks again.
Hi
I’m trying to get some code to write numbers when a set of numbers are found. I have copied an Excel sheet (just a very small example of what I use so I need specific cell locations to alter the code). To make it as clear as possible if I could draw your attention to the Excel sheet. I have a step by step process of what I’m trying to do.
Below I’m using the found number in cell B4.
1) The code must first search the range B4:B10, B13:B19 (need to be able to change) for any sets of numbers (1-1, 20-5, 7-7 etc).
- For each set of numbers found (B4 would be the first) the code must match cell A3 the “1-1” by searching specific cells H3 and H12 (cell locations in the code so it can be changed).
- The code also has to match 5.45 in cell C3 and search cells J3 and J12 (again need to be able to change in the code) for a match.
- It would find the 1-1 in cell H12 and 5.45 in cell J12.
- The last thing to do is match the line number which is in cell A4 which is 1 with line number 1 in cell H13. Write the contents of cells I13 to cell C4, cell J13 to cell D4 and cell K13 to cell E4.
2) The code would then move on to the next found set of numbers (cell B16 in this example) and repeat the process.
I really hope this is clear, not always easy to write it down. If it’s not clear please let me know so I can clarify it some more. Thanks so much for all your help.
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"WRITE NUMBERS TO CELLS.png","data-attachmentid":1216316}[/ATTACH]
It works, hey as always!!!
Thank-you so much.
Angela
Everything occurs within [SIZE=12px]cell rage E5:E14. I will add additional ranges in the expanded sheet but to get the code working on this sample range first. I should also mention that I could have up to 7 sets of number in this sample range at once that needs to moved down one cell. Thanks so much.[/SIZE]
Hi Carim
Nice to hear from you. I hope all is well with you. I just tested the code and I think I didn’t properly explain my sample Excel sheet. The cell rage is only E5:E14. I included “macro result” below the range in cells E18:E27 but it was only meant to show that 1-1 in cell E5 would become 1-2 in cell E6 and 10-2 in cell E12 would become 10-3 and appear in cell E13 (the original cell contents would be deleted). I tried to change the code but as always I’m doing something wrong.
Also, if I have say 5 sets of numbers in a cell range do I just have to add “Dim num3, 4 and 5 as long and change the other code locations?
Thank-you for all your help.
Angela
I need help with a change to my code. Currently it moves two cells of numbers (side by side) to the cell directly below it. I need it changed to move only the first cell down (in the cell range E5:E14 sample). I do not need the code to delete the cell contents to the right of it (cell range F5:F14 sample). There could be one of more sets of numbers in the cell rage (in the sample there are two sets and both have to be moved down by one cell).
This is only a sample sheet and so please don’t hard code the code. I need to be able to change the cell locations in the code. I have attached a sample Excel workbook with the expected result. I have tried to make the necessary changes with no luck and I hope this is an easy change. Thank-you for your assistance.
Sub MOVE1DOWN()
Dim n, sht As Worksheet, cell As Range, num, tmp, rngDest As Range
Set sht = ActiveSheet
n = sht.Range("J2")
For Each cell In sht.Range("E5:E14").Cells
tmp = cell.Offset(0, 1).Value
If cell.Value = n And tmp Like "*#-#*" Then
'get the first number
num = CLng(Trim(Split(tmp, "-")(0)))
Debug.Print "Found a positive result in " & cell.Address
'find the next empty cell in the appropriate row
Set rngDest = sht.Cells(num, sht.Columns.Count).End(xlToLeft).Offset(0, 1)
If rngDest.Column < 10 Then Set rngDest = sht.Cells(num, 10)
cell.Offset(0, 1).Copy rngDest
Rem Move the next row of cell contents to the next cell
In sht.Range(“F5:F14”).Cells
tmp = cell.Offset(0, 1).Value
If cell.Value = n And tmp Like "*#-#*" Then
Cells(irow + 1, icol).Insert Shift:=xlDown
Cells(irow, icol).Copy Cells(irow + 1, icol)
Cells(irow, icol).Clear
Exit For
End If
Next
End Sub[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"MARCH CHANGE.png","data-attachmentid":1215952}[/ATTACH]
Hi
I was trying to get this worked out myself and of course the answer was right in front of me!
Thank-you again. Your the best!
Hi Carim
The macro works really good but if we could make a small adjustment. When the code gets written from say cell I7 (has a 3.00 in it) to cell D20 right now it is adding to the existing number in cell E20 but not plus 3.00 but plus 1.00. It should be adding the number that is being written. Hey did you catch the above description: no hard coding, write and I’m stepping through the code etc, getting there.
As always thank-you so much all your help.
Hi Carim
Hope you're doing well. I have downloaded your file and will test on the weekend.
Have a wonderful weekend.
Angela
Uploaded Excel sheet