Extracting value from 1 of three columns where value exists

[satyen]

Hello,

I need to write some code in VBA (I will have incorporate it to my CASE statement) where it will look at a sheet called 'Source', in columns A,B,andC. There should only be a value in one of these columns. When it finds a value in the row I need to copy the value and paste it to cell A1 in 'Master' sheet. So for every row until the end of Source sheet it should go down row by row comparing the columns to see which column contains a value and copy it to the next row down in Master sheet.

Does this make sense?

thanks in advance

[figment]

give this a try

[vba]Sub source_to_master()
Dim a As Long, b As Long
With Worksheets("Source")
b = 1
For a = 1 To Excel.WorksheetFunction.Max(.Range("A65536").End(xlUp).Row, _
.Range("B65536").End(xlUp).Row, .Range("C65536").End(xlUp).Row)
If .Range("A" & a) <> "" Then
Worksheets("Master").Range("A" & b) = .Range("A" & a)
b = b + 1
ElseIf .Range("B" & a) <> "" Then
Worksheets("Master").Range("A" & b) = .Range("B" & a)
b = b + 1
Else
Worksheets("Master").Range("A" & b) = .Range("C" & a)
b = b + 1
End If
Next
End With
End Sub[/vba]

[tstav]

Maybe change the Else to ElseIf, to cater for any instances where all cells A, B, or C would be blank, so as not to have a blank line created in the Master sheet.

As for the ("X65536"), changing it to ("X" & .Rows.Count) would make the code "version proof".

[satyen]

Thanks the code works a treat. Yes I would need to cater for A, B and C being blank. Also if there are values in more than 1 column- bring up a Msgbox warning. What would be the extra code for these additional error trappings please.

Tstav- your second point what do you mean by "version proof"?

Thank you both.

[tstav]

Hi satyen,
in excel 2007 the 65536 row limit has increased to a little over one million rows.
So, if you have more than 65536 filled rows in your sheet, you can't use the ("A65536").end(xlUp) to find the last row, because you will get a false result.

By typing .Rows.Count you're telling the code to go find for itself the last row of the sheet (whichever that is) and then do the rest of your calculations. It's a lot more safe to do it this way.

[figment]

A quick questions; if all the cells are blank do you want to leave a blank spot on the master sheet? And if there are two entries do you want the user to pick and entry to use, and then modify both sheets? Or is the code to skill the entrees?

[satyen]

Hello,

Figment- 1)Yes if it is blank then leave blank spot on Master sheet. 2) There shouldn't be two entries and if there are, the code should enter a "?" for example in the Master sheet.

The Source sheet I am referencing will not be in the same workbook as Master. The Source sheet will reside in a workbook in a different directory, I will just be referencing it.

Tstav- thank you it makes sense now. Just don't know how to change the code to incorporate it.

[satyen]

Can anyone help with this?

[figment]

this should work, right now it assumes that the master sheet is in the same doc as the source sheet. you stated earlyer that this is false. if you can provide the name of the workbook that the master sheet resides in i can mode the code approprietly.

[vba]Sub source_to_master()
Dim a As Long, b As Long
With Worksheets("Source")
b = 1
For a = 1 To Excel.WorksheetFunction.Max(.Range("A" & Rows.Count).End(xlUp).Row, _
.Range("B" & Rows.Count).End(xlUp).Row, .Range("C" & Rows.Count).End(xlUp).Row)
If Count(.Range("A" & a & ":C" & a)) > 1 Then
Worksheets("Master").Range("A" & b) = "?"
ElseIf .Range("A" & a) <> "" Then
Worksheets("Master").Range("A" & b) = .Range("A" & a)
b = b + 1
ElseIf .Range("B" & a) <> "" Then
Worksheets("Master").Range("A" & b) = .Range("B" & a)
b = b + 1
Else
Worksheets("Master").Range("A" & b) = .Range("C" & a)
b = b + 1
End If
Next
End With
End Sub
[/vba]