PDA

View Full Version : return text in one column based on another one



jen122
01-08-2007, 09:13 AM
Hi,

I have a very long excel sheet. What I want to do is, depending on the input in one column (text), return a another text in another column, for the whole sheet.

I tried to with select case and if-then, but couldn't figure it out since I don't have much VB experience.

Can somebody help me out.

Thanks

jen122
01-08-2007, 09:20 AM
There will be two cases,

for example

if column 1 is either "A", "B", "C" or "D" write "X" in column 2
if column 1 is "E", "F" or G" write "Y" in column 2

but looping for each line of the sheet

ska67can
01-08-2007, 11:05 AM
Dim wkssht As Worksheet
Dim range1, r As Range
Dim fCell, lCell As Range
Set wkssht = Workbooks("Book1").Worksheets("Sheet1")
Set fCell = wkssht.Cells(1, 1)
Set lCell = wkssht.Cells(1500, 1).End(xlUp)
Set range1 = wkssht.Range(fCell, lCell)
For Each r In range1
With r
If .Value = "A" Or .Value = "B" Or .Value = "C" Or .Value = "D" Then
.Offset(0, 1) = "X"
ElseIf .Value = "E" Or .Value = "F" Or .Value = "G" Then
.Offset(0, 1) = "Y"
End If
End With
Next

lucas
01-08-2007, 11:39 AM
Option compare text will make it cap insensitive...won't matter whether it's a small or capital letter:
Option Explicit
Option Compare Text
Sub a()
Dim wkssht As Worksheet
Dim range1, r As Range
Dim fCell, lCell As Range
Set wkssht = Workbooks("Book1").Worksheets("Sheet1")
Set fCell = wkssht.Cells(1, 1)
Set lCell = wkssht.Cells(1500, 1).End(xlUp)
Set range1 = wkssht.Range(fCell, lCell)
For Each r In range1
With r
If .Value = "A" Or .Value = "B" Or .Value = "C" Or .Value = "D" Then
.Offset(0, 1) = "X"
ElseIf .Value = "E" Or .Value = "F" Or .Value = "G" Then
.Offset(0, 1) = "Y"
End If
End With
Next
End Sub

jen122
01-08-2007, 02:59 PM
thanks