activecell.entirerow.value2 problem

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAK
825/10/20091071MSCHI050MS Chillies Mixed 50g8006612n/an/aOrangeULGenovese3090000 3090 000040122198232685961
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:
Code:
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
Is Excel currently set for Automatic Calculation versus Manual?
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
Is Excel currently set for Automatic Calculation versus Manual?
Its currently set to manual

The process is kicked off bey a button

Code:
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
update
changing the code to:
Code:
        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

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
Try:

Code:
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

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,453
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
 

Forum statistics

Threads
1,081,832
Messages
5,361,586
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top