Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

if I have in cell "A1" any value like " my name " i have to separate tis value to many cells to be shown at cell "A2" (M) , cell"A3" (y), etc. . after that i have to look up for every cell value from cell "A2" to last cell which have last character on another range to get value which met this character .

the loop is done till separating cell "A1" value to many cells but only making lookup for cell "A2" only and not making loop for the next cells .

Sub Macro1() LENGHTH = Len(Range("A1")) CELL_START = ("2") WORD_LEN = 1

  Do While LENGHTH <> 0
    Range("A" & CELL_START).VALUE = mid(Range("A1"), WORD_LEN, 1)
    CELL_START = CELL_START + 1
    LENGHTH = LENGHTH - 1
    WORD_LEN = WORD_LEN + 1
  Loop

  If ActiveWorkbook.Sheets("SHEET1").Range("A2") <> "" Then
     ***Do While ("a2" & " LENGHTH") = ""
       Range("B2").VALUE = WorksheetFunction.VLookup(Range("A2"), Sheet1.Range("G3:H15"), 2, False)
     Loop***

  Else
  End If

End Sub
share|improve this question

closed as not a real question by Mehper C. Palavuzlar, Tog, Dave M, CharlieRB, Scott Apr 16 '13 at 20:28

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
This question is a duplicate of superuser.com/questions/583423/… which has been nominated to be re-opened. –  Tog Apr 16 '13 at 11:15
    
You shouldn't re-post questions: superuser.com/questions/583423/… Please, in the future, edit them and have them reopened. @Tog I deleted it now, as this one already has an answer. –  slhck Apr 16 '13 at 11:15

1 Answer 1

Here is the code for taking the "name" entered in A1 and spreading the letters across the cells to the right of the "name". The code shown will allow you to list a bunch of names in column A, and the macro will spread the letters for all names listed.

Dim LENGHTH As Integer
Dim zWord As String

Sub Macro1()
    Range("A1").Select
    zWord = Range("A1").Value
    Do While zWord <> ""
        LENGHTH = Len(zWord)
        For i = 1 To LENGHTH
            ActiveCell.Offset(0, 1).Select
            ActiveCell.Value = Mid(zWord, i, 1)
        Next i
        ActiveCell.Offset(1, -LENGHTH).Select
        zWord = ActiveCell.Value
    Loop
End Sub

I'm not sure what you are trying to accomplish in the second section of the macro, but you may want to use "hlookup" rather than "vloopup".

share|improve this answer
    
ok thanks a lot but my problem THAT WHEN I START TO MAKE LOOP FOR )VLOOKUP( OR )HLOOKUP( TO GET EVERY LETTER ( WHICH SEPERATED ON THE FIRST SECTION ) VALUE AS I FOLLOWED IN ANOTHER RANGE . IT IS GET TO ME THE VALUE FOR THE FIRST LETTER ONLY AND CANT LOOP TO ANOTHER CELLS TO GET VALUES . –  M.gamal Apr 16 '13 at 12:14

Not the answer you're looking for? Browse other questions tagged or ask your own question.