activecell.entirerow.value2 problem

[MartinL]

Hi

I am having problems with a value check.
Basically:
I have pulled data from a csv file into my workbook. During this cut'n'paste exersize I have found that some values are n/a.

When this happens I want the value of the cell 4 to the right (on the same row) to replace the n/a.

for example:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
8	25/10/2009	1071			MSCHI050			MS Chillies Mixed 50g			800		66		12		n/a		n/a			Orange	UL		Genovese					3090			000 3090 0000	40122	19823	268596	1
inDATA

Here Q8 and S8 have n/a values I want Q8 to be 66 and S8 to be 12

My code for doing this is:

For Each Colour In TestForColour.Cells
    If Colour.Value <> "" Then
        Intersect(ActiveCell.EntireRow, Columns("A:AJ")).Copy
        wbThis.Activate
        Sheets("PROPHETDATA").Select
        Selection.PasteSpecial Paste:=xlPasteValues
        If (ActiveCell.EntireRow.Value2(1, 17)) = "n/a" Then ActiveCell.EntireRow.Value2(1, 17) = ActiveCell.EntireRow.Value2(1, 13)
        If (ActiveCell.EntireRow.Value2(1, 19)) = "n/a" Then ActiveCell.EntireRow.Value2(1, 19) = ActiveCell.EntireRow.Value2(1, 15)
        Range("A" & A).Select
        ActiveCell = DateValue(ArrivalDate)
        A = A + 1
        Range("B" & A).Select
        ActiveWindow.ActivatePrevious
        
    End If
b = b + 1
Range("U" & b).Select
Next Colour

the module compiles ok and runs though it ok
checking the values they are correct but the cell does not update

any ideas

Martin

 

[jim may]

Is Excel currently set for Automatic Calculation versus Manual?

 

[MartinL]

Is Excel currently set for Automatic Calculation versus Manual?

Its currently set to manual

The process is kicked off bey a button

ActiveSheet.Unprotect
Application.EnableEvents = False
Application.ScreenUpdating = False
With Application
     .Calculation = xlCalculationManual
End With
.
.
.
.
Application.EnableEvents = True
Application.ScreenUpdating = True
ActiveSheet.Protect
With Application
    .Calculation = xlAutomatic
End With

 

[MartinL]

update
changing the code to:

        With Application
            .Calculation = xlCalculationAutomatic
        End With
        Application.EnableEvents = True
        Application.ScreenUpdating = True

        If (ActiveCell.EntireRow.Value2(1, 17)) = "n/a" Then ActiveCell.EntireRow.Value2(1, 17) = ActiveCell.EntireRow.Value2(1, 13)
        'Me.Calculate
        If (ActiveCell.EntireRow.Value2(1, 19)) = "n/a" Then ActiveCell.EntireRow.Value2(1, 19) = ActiveCell.EntireRow.Value2(1, 15)
        'Me.Calculate
           'turn off auto calc
        With Application
            .Calculation = xlCalculationManual
        End With
        Application.EnableEvents = False
        Application.ScreenUpdating = False

has not made any difference. As you can see I also tried the Me.Calculate command but excel did not like it.

Martin

 

[jim may]

Try:

Sub Test()
If (ActiveCell.EntireRow.Value2(1, 17)) = "n/a" Then Cells(ActiveCell.Row, 17) = Cells(ActiveCell.Row, 13)
        If (ActiveCell.EntireRow.Value2(1, 19)) = "n/a" Then Cells(ActiveCell.Row, 19) = Cells(ActiveCell.Row, 15)
        Range("A" & A).Select
End Sub

 

[MartinL]

Thanks Jim

That did it. can you explain the difference between your and my code.
I see you wrapped a Cells around mine bt why did it need it???

Martin

 

[jim may]

Once the condition was met ("n/a" = True) you were saying
Copy a designated cell of the EntireRow to a Designated cell of the EntireRow
Excel CHOKES on such instructions.
You wanted when the condition was met ("n/a" = True) to
Copy a single designated cell of the Activecell.row to another single cell of the same Activecell.row. Use of the Entirerow took you UP to too high of a level (multiple cells)
where you are dealing with single cells at this point.
Does this make sense?
Hope So !!!

Jim

 

[MartinL]

It does now
thanks again

Martin