excel macro 2 if loops

  • Thread starter Thread starter Wenom
  • Start date Start date
W

Wenom

I'm looking for someone to help me write an easy macro
for excel. I don't know VBA at all and think that it's
impossible to do it with stepwise programmer. The thing
is that i have a file that has many cells. Each cell
starts with Q: or A: or nothing. The file always starts
with a cell that starts with Q:. I want the macro to do
the following.

1. I see the cell starting with Q: so I add Q: at the
beginning of all the cells after this cell.
2. I do it until I find a cell taht starts with A:
3. I find the cell starting with A: and I copy A: at
the beginning of all the cells after the A: cell until
I find the cell starting with Q:
4. Then i do step 1

That would be it. Help me please to write it.
 
Wenom,

I'm guessing that these are drive letter specs. This one looks for A: or Q:
Set up ConvertRange for your range of cells

Sub InsertDriveSpec()

Dim ConvertRange As Range
Dim thing As Range
Dim CurrentDrive As String

Set ConvertRange = Range("A2:A10") ' change to your range
For Each thing In ConvertRange
Select Case UCase(Left(thing, 2))
Case "A:"
CurrentDrive = "A:"
Case "Q:"
CurrentDrive = "Q:"

End Select
If Left(thing, 2) = "A:" Or Left(thing, 2) = "Q:" And thing <> "" Then
Else
thing.Value = CurrentDrive & thing.Value
End If
Next thing
End Sub

This one looks for any drive letter, not just A or Q

Sub InsertDriveSpec2()

Dim ConvertRange As Range
Dim thing As Range
Dim CurrentDrive As String

Set ConvertRange = Range("A2:A10")
For Each thing In ConvertRange
If Mid(thing, 2, 1) = ":" Then
CurrentDrive = Left(thing, 2)
End If
If Mid(thing, 2, 1) = ":" Then
Else
thing.Value = CurrentDrive & thing.Value
End If
Next thing
End Sub
 
You may want to adapt this code. It will take the first letter of a cell
and test for any character. If it finds it it will append it to the
beginning of all data below until it encounters another cell that starts
with a letter then it will append all cells below that with the new letter.

It works on a pre-selected range

Sub addLetterToFront()
Dim myCell As Range
Dim sFirstLetter As String
For Each myCell In Selection
If IsNumeric(Left(myCell, 1)) = False Then
sFirstLetter = Left(myCell.Value, 1)
Else
myCell.Value = sFirstLetter & myCell.Value
End If
Next myCell
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Dnia Sun, 5 Sep 2004 15:38:05 +0100, Nick Hodge
You may want to adapt this code. It will take the first letter of a cell
and test for any character. If it finds it it will append it to the
beginning of all data below until it encounters another cell that starts
with a letter then it will append all cells below that with the new
letter.

It works on a pre-selected range

Sub addLetterToFront()
Dim myCell As Range
Dim sFirstLetter As String
For Each myCell In Selection
If IsNumeric(Left(myCell, 1)) = False Then
sFirstLetter = Left(myCell.Value, 1)
Else
myCell.Value = sFirstLetter & myCell.Value
End If
Next myCell
End Sub
Click to expand...

I don't know VBA at all. Where in this module should I type my letters Q:
and A:?
 
Sorry

Wenom, missed the colons...for speed use Earl's code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
I though that the thing about drive has something to do with my HDD. It
works fine except for the fact that once at the time pobably when the line
starts with a number it says: type mismach. Fortunately there are few
cells starting with numbers so within 37000 cells I had to edit the cell
manually only 4 times.

Thank you very much for your help.

By writing this macro for me you've completely convinced me that basic
programming skills can be a real time saver. I'll stick to the books and
learn vba. Can you advise me a nice book for beginners?
 
Back
Top