VBA code is working for New entry, Not working for Old entry...

[bala04msw]

Dear Forum,

I have a dynamic master sheet with 5000 individual details. I have written and applied a VBA code for this master sheet to get automation result of the status of visit (either new visitor or repeat visitor) after did the entry of 5000. The VBA code shows me good result for new entry, But for old entry, i have to select (press F2 option)each name and enter, then only VBA code works well.

I want that VBA code work automatically. How should i do that? Is it refresh option?

Kindly do the needful.

Thanks in advance.

[Kenneth Hobson]

It depends on your macro code. I suspect that you are using a macro in the sheet to act on a selection change event. As such, only old data renewed/refreshed as you detailed would activate it. If coded to act on more than just one cell, select all of the old data and then cut/paste to fire the event. Some coders will make the routine exit if more than one cell was changed at once.

You can view a sheet's macro code by right clicking the sheet's tab > View Code.

[bala04msw]

Dear Mr. Kenneth,

Yes, I have already tried Cut and Paste option, that option is not also working. Could you please refer, any code should i add in the existing code?

Thanks

[Kenneth Hobson]

I would have to see your code. As I said, I expect that you have code such as:

Please Login or Register  to view this content.

I bet that cut/paste for one cell does work...

[bala04msw]

Dear Mr. Kenneth,

Yes, you are exactly right. The following is my code. And Could you please help me for this requirement also.http://www.excelforum.com/showthread.php?t=1157803

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 5 Then Exit Sub
If Target.Column < 2 Then Exit Sub
If Target.Column > 3 Then Exit Sub
If Target.Rows.Count > 1 Then Exit Sub
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Range("B" & Target.Row) = "" Or Target = "" Then
Range("H" & Target.Row) = ""
Range("L" & Target.Row) = ""
Else
Range("H" & Target.Row) = Evaluate("=IF(COUNTIF($C$5:C" & Target.Row & ",C" & Target.Row & ")=0,"""",IF(COUNTIF($C$5:C" & Target.Row & ",C" & Target.Row & ")=1,""New"",IF(COUNTIF($C$5:C" & Target.Row & ",C" & Target.Row & ")>=2,""Repeat"")))")
Range("L" & Target.Row) = Evaluate("=IFERROR(IF(H" & Target.Row & "=""NEW"",""OD/GJM/""&YEAR(B" & Target.Row & ")&""/""&TEXT(COUNTIF($H$5:H" & Target.Row & ",""NEW""),""0000""),INDEX(L:L,MATCH(C" & Target.Row & ",C:C,0),0)),"""")")
End If
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Thanks and looking forward your support.

[Kenneth Hobson]

Always test on a backup copy. Put the 2nd Sub in a Module and copy the sheet code to each sheet as needed, method (2). Maybe:

Please Login or Register  to view this content.

[bala04msw]

Dear Mr. Kenneth,

Thanks for your help. so, i have to insert the module for that worksheet and paste this code and name the macro for it. then go to worksheet and assign macro in the respective Column H and L.

Am i right?

Thanks

[bala04msw]

Dear Mr. Kenneth,

I have tried with this code.. It is not working.. Kindly help me on this.

Thanks

[Kenneth Hobson]

You don't know how to insert a Module into the VBAProject? In the Visual Basic Editor (VBE) select menus Insert > Module. Paste the 2nd Sub in it.

Then just add the first Sub to each of the sheets as needed. You can doubleclick the sheet object in the Project Explorer of the VBE. If Project Explorer is not shown, in VBE, View > Project Explorer. Or, right click a sheet's tab, View Code, and paste there, same thing.

Try it for one worksheet first. Then just paste that first Sub to each sheet as needed.

As for what "working" means, I don't know. Obviously, it should "work" just like your current method only it allows it to act on more than one cell changed in column B in rows 5 and up.

IF you need to Debug it and see what it does line by line, add a stop in the far left column of the code by clicking at the first line that adds a red marker dot. Then press F8 to execute each line. Hover cursor over executed line to see values.

If all that fails, then make a simple example file and attach it. Click the Go Advanced button in lower right of a reply and then the paperclip icon on the toolbar to Browse and Upload the file.

[bala04msw]

Dear Mr. Kenneth,

Thanks a lot for your support and guidance. Its working perfectly. More over, as i requested you already, could you please help me on this link also
http://www.excelforum.com/showthread.php?t=1157803

Thanks a lot