Excel macro help

Question

I used a macro to get me this far but I can't see to figure this one out.

     1100 	REINFORCING STEEL DWGS
     1101	COE APPROVAL
     1102	RETURN
     1110 	FORMWORK DWGS                              
     1111	COE APPROVAL
     1112	RETURN

I have this situation on 20 pages. I want to copy the data in B1 to B2 and B3. But I need to be dynamic so it copies the string in the cell because it changes through the spreadsheet. This should be the end result.

     1100 	REINFORCING STEEL DWGS
     1101	COE APPROVAL REINFORCING STEEL DWGS
     1102	RETURN REINFORCING STEEL DWGS
     1110      FORMWORK DWGS                              
     1111	COE APPROVAL FORMWORK DWGS
     1112	RETURN FORMWORK DWGS

Thanks for you help

Answer

Stephen

if

A1: 1100 B1: REINFORCING STEEL DWGS

and so forth and I can assume any cell in column A ending in zero needs to have its corresponding value in the same row in column B appended to all subsequent values (in column B) until another cell is found in column A ending in Zero. Then the process repeats

Sub copyText() Dim r As Range, s As String, s1 As String, s2 As String Set r = Range("A1", Cells(Rows.Count, "A").End(xlUp)) For Each cell In r

s = Application.Trim(cell.Text)
If Right(s, 1) = "0" Then
   s1 = Application.Trim(cell.Offset(0, 1).Text)
Else
   s2 = Trim(cell.Offset(0, 1).Value)
   cell.Offset(0, 1).Value = s2 & " " & s1
End If

Next End Sub

produced the output you show.

Test it on a copy of your worksheet

Question

Dear Mr. Heritage,

I am creating a spreadsheet which shows spreads between various numbers between months. What I have is one sheet which shows all the months of the year, and then 10 products for those months.

What the cell for product one in January for example should say is the maximum of a set of numbers from a secondary sheet, using the =MAX function.

As you can imaging, having 120 sets of up to 30 separate cells to MAX per spread is reasonably difficult to navigate and add to at speed, so what I was wondering was if there was a macro which when I double clicked on a particular cell, it could bring up a different sheet (ideally in a smaller window), inside which there would be lets say 3 columns and 30 rows which I could then type in, press ok, or exit, and the mini sheet window would exit, and the maximum of the newly entered figures would appear in the cell.

Obviously the least complex way to do this would be to create multiple sheets and so forth, but as adding the numbers each day has to be done very quickly, if I could just double click on the cell, and the 'mini-window' just showing those figures would appear, this would be hugely useful, as it would save a great deal of time.

If you could shed any light on this, I would be very grateful.

Best,

Freddie

Answer

I¡¯m slightly puzzled about why the data needs to be typed ¨C if this data exists somewhere already thencould open it ¨C but that said, I would do this with a userform and some vba ¨C it wouldn¡¯t be a sheet as such, just a table of the size you specified.

email if it helps (files can be sent that way) is aidan.heritage@virgin.net

Question

Dear Mr. Roberts,

I am creating a spreadsheet which shows spreads between various numbers between months. What I have is one sheet which shows all the months of the year, and then 10 products for those months.

What the cell for product one in January for example should say is the maximum of a set of numbers from a secondary sheet, using the =MAX function.

As you can imaging, having 120 sets of up to 30 separate cells to MAX per spread is reasonably difficult to navigate and add to at speed, so what I was wondering was if there was a macro which when I double clicked on a particular cell, it could bring up a different sheet (ideally in a smaller window), inside which there would be lets say 3 columns and 30 rows which I could then type in, press ok, or exit, and the mini sheet window would exit, and the maximum of the newly entered figures would appear in the cell.

Obviously the least complex way to do this would be to create multiple sheets and so forth, but as adding the numbers each day has to be done very quickly, if I could just double click on the cell, and the 'mini-window' just showing those figures would appear, this would be hugely useful, as it would save a great deal of time.

If you could shed any light on this, I would be very grateful.

Best,

Freddie

Answer

Freddie

Sorry to be late getitng back to you, but I just got back to my computer. You can do what you want to do with a macro, but unfortunately I am not a macro expert and its beyond my abilities. I suggest you reask the question of another allexperts person and ck the resumes for expertisise in macro. I am sure someone can get you a good answer.

Richard