Hi,

I have two worksheets, LETFILE and LETconsol, LETFILE is a data file
that I create
a template for user to input data and LETconsol is a collect data file
that I collect
data from many LETFILE(1,2,3....). The problem is I cannot store text
from LETFILE cell E5 to variable and paste it to LETconsol. I create
this text as a drop down list
from data validation for users to select which option do they need to
put into this
cell. Could you please help me how to fix it ? Thank you.



<<<<<< Here is my code


Worksheets("LET").Activate


On Error Resume Next
Set Letsheet = ThisWorkbook.Sheets("LET")
Row = 5 'LET Row
iRow = 2 'LETconsol Row
Do Until IsEmpty(Letsheet.Cells(Row, 2))
Windows(LETFILE).Activate
Worksheets("LET").Activate
ChangeLosstype = Letsheet.Range(Row, 5).Formula

'at this point I try to change many properties eg.
Letsheet.Rnge(Row,5).Text or
' .Value ...............It doesn't work the result is empty or it
returns " ".
x = MsgBox(ChangeLosstype)
Select Case ChangeLosstype
Case ChangeLosstype = "Option 1"
Losstype = 1
Case ChangeLosstype = "Option 2"
Losstype = 2
Case ChangeLosstype = "Option 3"
Losstype = 3
Case Else
x = MsgBox("An Error Occur ...There Loss Type is not correct.....Please
recheck")
End Select

'Start at Select Range C5:D5 from "LET" worksheet
Range(Cells(Row, 3), Cells(Row, 4)).Select
Selection.Copy

'Switch to LETconsol.xls
Windows("LETconsol.xls").Activate
Sheets("test").Select
'Start Select Range B2 from "LETconsol" worksheet
Range(Cells(iRow, 2)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range(Cells(iRow, 4)).Select


'paste text variable into cell
ActiveCell.Formula = Losstype
iRow = iRow + 1
Row = Row + 1
'End If
Loop


--
jafery


------------------------------------------------------------------------
jafery's Profile: http://www.excelforum.com/member.php...o&userid=27645
View this thread: http://www.excelforum.com/showthread...hreadid=471996

The methods you mention (Letsheet.Range(Row,5).Text or .Value) should work if
everything else is correct. I can only think of two possible reasons why
they would not work: 1) your code is not actually pointing at cell E5 when
you think it is, or 2) the value of E5 is blank; i.e. the selection from the
dropdown list you mention is not being stored in cell E5.

Here are some suggestions to try to find the problem:

First, are you sure you are pointing at the right cell? From the code you
gave it looks like you are, but try breaking the code on the line that has
the problem and try ? Letsheet.Range(Row,5).Address in the immediate pane to
make sure. Also, try ? Letsheet.Range(Row,5).Value and ?
LetSheet.Range("E5").Value to see what you get.

Then, since you mention a drop-down: Is your dropdown box actually linked
to E5? In other words, are you sure the selected value is actually going
into cell E5? If it is not, that would explain why E5 is empty.
--
- K Dales


"jafery" wrote:


Hi,

I have two worksheets, LETFILE and LETconsol, LETFILE is a data file
that I create
a template for user to input data and LETconsol is a collect data file
that I collect
data from many LETFILE(1,2,3....). The problem is I cannot store text
from LETFILE cell E5 to variable and paste it to LETconsol. I create
this text as a drop down list
from data validation for users to select which option do they need to
put into this
cell. Could you please help me how to fix it ? Thank you.



<<<<<< Here is my code


Worksheets("LET").Activate


On Error Resume Next
Set Letsheet = ThisWorkbook.Sheets("LET")
Row = 5 'LET Row
iRow = 2 'LETconsol Row
Do Until IsEmpty(Letsheet.Cells(Row, 2))
Windows(LETFILE).Activate
Worksheets("LET").Activate
ChangeLosstype = Letsheet.Range(Row, 5).Formula

'at this point I try to change many properties eg.
Letsheet.Rnge(Row,5).Text or
' .Value ...............It doesn't work the result is empty or it
returns " ".
x = MsgBox(ChangeLosstype)
Select Case ChangeLosstype
Case ChangeLosstype = "Option 1"
Losstype = 1
Case ChangeLosstype = "Option 2"
Losstype = 2
Case ChangeLosstype = "Option 3"
Losstype = 3
Case Else
x = MsgBox("An Error Occur ...There Loss Type is not correct.....Please
recheck")
End Select

'Start at Select Range C5:D5 from "LET" worksheet
Range(Cells(Row, 3), Cells(Row, 4)).Select
Selection.Copy

'Switch to LETconsol.xls
Windows("LETconsol.xls").Activate
Sheets("test").Select
'Start Select Range B2 from "LETconsol" worksheet
Range(Cells(iRow, 2)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Range(Cells(iRow, 4)).Select


'paste text variable into cell
ActiveCell.Formula = Losstype
iRow = iRow + 1
Row = Row + 1
'End If
Loop


--
jafery


------------------------------------------------------------------------
jafery's Profile: http://www.excelforum.com/member.php...o&userid=27645
View this thread: http://www.excelforum.com/showthread...hreadid=471996