VBA Code to loop through a condition & Refresh Pivot tables

[Ajit Kiran]

Hello Guys,

I'm looking for a VBA code which helps loop through a column and assigns a value in another column if a condition is met. For eg., if the value in D3 is "C" then Value in E3 must automatically turn to "Same day cancel." I'm able to accomplish this in a particular cell but not in a whole range.

Sub valueinput()
 
If Range("C3").Value = "C" Then Range("E3").Value = "Same day cancel"
 
 
 
End Sub

So, the code should be able to loop through a whole range in Col C i.e from C1 to C1000 and accordingly input a value in Col E should the criteria of Value "C" is met there.

Please would someone be able to help me with this?

Thanks much,
Ajit Kiran

 

[NARAYANK991]

Hi Ajit ,

This can be done without using a loop ; try this code :

Public Sub Insert_Text()
          Const INSERTCOLUMN = 5                                                '  Column E is column #5
          Dim datarange As Range
          Set datarange = ThisWorkbook.Worksheets("Sheet1").Range("C1:C1000")    '  Change to suit
         
          datarange.Offset(, INSERTCOLUMN - datarange.Column).FormulaArray = Evaluate("=IF(" & datarange.Address & "=""C""," & """Same day cancel""" & " ,"""")")
End Sub

Narayan

 

[Ajit Kiran]

Hey Narayan!


Thanks a ton for the code. It indeed works like a charm. But the only thing is it's taking longer than usual (compared to manually filtering out and updating it to "Same Day Cancel"). Is there any way to reduce the time taken cos I have about 5000 or increasing cell range to look into?

Also, is there any code to input the 'Same day cancel" comment in Col E for the first of the occurances and then do an autofill downwards after filtering for "C" in Col C? Would that help reduce time anyway?

Besides the above, I'm also looking for a code which refreshes a dynamic pivot:

I'd in fact need the last item in the fields to be selected. I get multiple options like the below :

1. All
2. (blank)
3. 0
4. Date eg. 8/15/2013

And I only want the date to be selected among the 4 fields above. Would it be possible to get a code for this pls?
And if I have 3 pivots of the same design how do I include all 3 pivots in the code?

Thanks again in advance!

 

[Abhijeet R. Joshi]

I think you can do this using a formula and then autofill it.
=IF(C1="C","Same Day Cancel","")

Hope this helps you....