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
On Error Resume Next
Set Letsheet = ThisWorkbook.Sheets("LET")
Row = 5 'LET Row
iRow = 2 'LETconsol Row
Do Until IsEmpty(Letsheet.Cells(Row, 2))
ChangeLosstype = Letsheet.Range(Row, 5).Formula
'at this point I try to change many properties eg.
' .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
x = MsgBox("An Error Occur ...There Loss Type is not correct.....Please recheck")
'Start at Select Range C5:D5 from "LET" worksheet
Range(Cells(Row, 3), Cells(Row, 4)).Select
'Switch to LETconsol.xls
'Start Select Range B2 from "LETconsol" worksheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
'paste text variable into cell
ActiveCell.Formula = Losstype
iRow = iRow + 1
Row = Row + 1