Creating a questionnaire from range.

[fabrecass]

Hi,

trying to manipulate a range of data, not going too well so far, my VBA skills suck.

i want to create a macro that removes a few cells with unwanted data in from a range that can vary in length, copies that range and pastes special:transpose onto a new worksheet, and turns each cell into a question by adding a question mark to the end of each cell (=cell&"?"), then some basic formatting i think i could manage myself.

My macro gets as far as pasting onto the new worksheet, but i can't for the life of me figure out how to add the question marks!

Hope this makes sense. can anyone help me?

*edit: Sample workbook attached, apologies for breaking the rules. The terms "internal manufacturer guarantee", "group", "group name", and "group product" must be removed from range A14:40, though this range can vary in size. Sheet 1 shows where my macro crashes.*

[royUK]

Can you set out the steps that your code is supposed to be doing

[Domski]

This will add a ? to each value on the the new sheet:

With Range("A1").CurrentRegion
    
    .Value = Evaluate(.Address & "&""?""")
    
End With

Dom

[fabrecass]

1. delete cells containing "group" and cell containing "internal manufacturer guarantee"
2. Insert cells containing "series", "cat" and "product" at start of range
3. copy range: cell containing "series":cell containing "EAN/Barcode no"
4. paste special:transpose onto new worksheet
5. add question mark to end of text in each cell
6. paste values.

I'm not sure if i'd being doing this in the 'correct' order, i'm sure you guys can simplify it somewhat. Hope that helps?

[Domski]

Full code can be edited to:

Sub DDS_Create()
    
    Cells.Find(What:="internal manufacturer guarantee", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Delete Shift:=xlUp
    Rows("14:16").Delete Shift:=xlUp
    Range(Range("A14"), Range("A14").End(xlDown)).Copy
    Worksheets.Add
    Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    With Range("A1").CurrentRegion
        .Value = Evaluate(.Address & "&""?""")
    End With
    
End Sub

Dom

[fabrecass]

Thanks for your swift reply, however the group values are not necessarily in A14:16, is there anyway that can be edited to delete relative to the cell number they may be in?

[Domski]

Sure, can you explain the logic?

Dom