Posts by mchilapur

Re: VBA_To find a cell value in range_Worksheet change event

Great...Works flawlessly..Thanks a lot for your code and Logic...:)
Thanks for your Tip as well..
Have a great day sir.

Re: VBA_To find a cell value in range_Worksheet change event

Thanks a lot for your response...But i have one more small issue.
Your Code targets column 3 only, but i have an situation where in user can paste data from column 1 to column 3 from other database..
To put it in simple words, we have database where data is available till first 3 columns..Users copy them and paste it till first 3 columns..
During this situation you code doesnt appear to update 'Single' and "multiple'.

Please help me to fix this..
many thanks in advance...

Dear all,
Somewhere i got messed up with below code. Below is something what i am trying to achieve.

Whenever a user enters new value in any cell of column C:C, vba code must check for only that call value already exists in entire column C:C, if its found, then it must set corresponding column I:I cell value "Multiple". If not found, it must set to "Single".

Challenges faced:
1) Currently i am looping through entire C:C column which is not correct. I want value for cell in column I:I to be updated for new entries in columns C:C only.
(I have values update 5000 + rows..So looping slows down the process)
2) User may enter multiple cell values in one shot (Like, he copies 10 rows of data from some other database and pastes it directly into Excel sheet upto column H:H).

How to i achieve it..? Please help..Attached is the sample work book and has got code it (Which basically doesn't work).

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column = 3 Then
    Dim lastRow As Long
    Dim matchFoundIndex As Long
    Dim iCntr As Long
    Dim Rng As Range
    Dim cell As Range
    
    Set Rng = Worksheets("Tracker").Range("C3", Worksheets("Tracker").Cells(Rows.Count, "C").End(xlUp))
    For Each cell In Rng.Cells
        If cell.Offset(0, 6).Value = "" Then
            matchFoundIndex = WorksheetFunction.Match(cell.Value, Rng, 0)
            If cell.Value = matchFoundIndex Then
                cell.Offset(0, 6).Value = "Single"
            Else
                cell.Offset(0, 6).Value = "Multiple"
            End If
        End If
     Next cell
End If


End Sub

Please note: This post is a cross post from below link.
http://www.excelforum.com/exce…rksheet-change-event.html

Re: Create master file with value one below each other

Please attach sample file...

Re: Excel VBA to extract part of string and concatenate them with "," (Unique values

sktneer..Yes your codes works perfectly..:) Thanks for your response..

Can you please help me to correct previous code..?
Is it possible to check if pc<>pcDiv1 instead of pc<>pc1..?

Anyhow this thread is solved..:)

pc <> pc1]Then
 pcDiv1 = pcDiv1 & "," & pc

Re: Excel VBA to extract part of string and concatenate them with &quot;,&quot; (Unique values

Hello Stephen,
Now i realized by mistake...Thanks your code..:)
And sorry for cross-posting..I am not among those who just put in their concern and let others work on it..
I try to understand the logic and realize where i commit mistake..

Your code still make "ALS" as duplicate entry....

I think it still requires modification..Please help..

Re: Excel VBA to extract part of string and concatenate them with &quot;,&quot; (Unique values

Thanks for your response..
I want this value to be stored as a variable..I am upto the below code, but i am failing to pull UNIQUE values.
Can anyone please help..?

Sub test()Dim aa As String
Dim dd As Variant




For Each cell In ActiveSheet.Range("A2", ActiveSheet.Cells(Rows.Count, "A").End(xlUp))
If cell.Value <> "" Then
    aa = Left(cell.Value, 3)
    If dd = Empty Then
        dd = aa
            'ElseIf InStr(aa, dd) Then
            ElseIf Not IsError(Application.Match(aa, dd, 0)) Then
            GoTo iteration:
            Else: dd = dd & "," & aa




    End If
  End If




iteration:
Next cell
End Sub

Dear all,

In the data available in attachment, i am trying to extract starting 3 letters of each cell value and add only UNIQUE values with ',' separator.

I tried to find logic with below code but i know it fails.
Can anyone please help me to correct it or provide fresh code to achieve the intent.
Output required is as shown in the attachment.

Many thanks.

Just in order to get best possible solution, this querry is cross posted @
http://www.excelforum.com/showthread.php?t=1146299&p=4425730#post4425730

Option Explicit


Sub extractpC()
    Dim pc As String
    Dim pc1 As String
    Dim pcDiv1 As String
    Dim cell As Range
    
    pc1 = ""
    
     For Each cell In ActiveSheet.Range("A2", ActiveSheet.Cells(Rows.Count, "A").End(xlUp))
     
            pc = Left(cell.Value, 3)
                        'MsgBox pc
                    If pc1 = "" Then
                        pc1 = pc
                        ElseIf pc = pc1 Then
                            pcDiv1 = pc1
                            Else: pcDiv1 = pc & "," & pc1
                     End If
'                pcDiv1 = pcDiv1 + pcDiv1
        End If
    Next cell
    MsgBox pcDiv1
End Sub

Dear all,

Is it possible to make a cell hyperlinked to samesheet..?

I have recorded a macro below, When i run this procedure it makes the cell hyperlinked. But when i key-in in some other procedure, its doent make it hyperlinked.
I need to keyin this code in some other module, which at the end must make the cell hyperlinked

Any suggestions..?

Sub Macro1()'
' Macro1 Macro
'




'
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "Report!B6"
End Sub

Dear all,

PLease review the attachment and code it carries in it.

This code counts the data from sheet2 and stores it in 'Count' variable and in-turn inserts that value in sheet1 (D3).

I was this D3 value (Basically count value) to be hyper-linked to Data it counted. Is it possible to achieve..?

My basic intent is, if the user clicks on this value (hyperlinked) he must get all the data of that count.

This is first time i am trying this kind of, kindly help.
Any alternate suggestions are most welcome.

Thanks

Sub test()Dim tday As Date, tend As Date
Dim Count As Integer
Dim cell As Range




tday = Date
tend = tday + 4
Count = 0
    
        Sheet2.Activate




        For Each cell In ActiveSheet.Range("A2", ActiveSheet.Cells(Rows.Count, "A").End(xlUp))
               If cell.Value = 1 And cell.Offset(0, 1).Value >= tday And cell.Offset(0, 1).Value <= tend Then
                    Count = Count + 1
                End If
         Next cell
                    
Sheet1.Range("D3").Value = Count




End Sub

Hello all,
I use below code to select header by name "XYZ".
Few times it gets changed to "XYZA".
I need "Application.Match" code to select either of these header whichever is available, Like Application.Match("XYZ" or "XYZA").

How best i can modify it..?

Cells(1, Application.Match("ABC", Range("1:1"), 0)).Select

Re: Open UserForm as New instance/Make other work editable when userForm is open

Great...That worked...Thanks a lot.

Dear all,
When userForm is open, other workbooks usually hang-up. We cannot select any data for other workbooks until we close userform.
Is it possible to open userform as new instance.?
My basic intent is to make other workbooks editable when userForm is open.

Thanks
madvesh

Re: Excel Function: Multiple IF conditions, Must return &quot;&quot;

Works great....Thanks a lot..:)

Re: Searching for a Solution to a Long-Standing Excel Problem

Also you can try:

For example Excel can get confused about the actual range of your worksheets.
Press CTRL + END and see where the selection ends up.
Best case scenario it will be right underneath the the bottom-most right-most used cell. However, sometimes it will be way off of that with a lot of empty space to your actual data.
Select all the empty cells, then right-click and select Delete (this is different from pressing the DEL key!). Save your file and test again.
Do this for all the worksheets of your workbook to clear unnecessary “empty cell” bloat.

I already mentioned that formatting can get funky. And with more and more formatting it can get more and more problematic. If you were conservative with formatting clearing and resetting it shouldn’t take too long and might help.
* Definitely create a backup of the entire workbook beforehand.
For example if you have a header row with a certain formatting select the entire row, then on the Home tab in the editing group click Clear > Clear Formats. Then reset the format.
Actually, a good place to start might be the non-formatted cells! Probably most of your data entries are in the standard format. Select all of it and clear the formats to make sure it isn’t “made to look like standard format” ~ this will also clear data formatting (e.g. Time, Date) so this is something one has to keep in mind.

Finally I would clear the Formula error checking.
On the Formula tab there is a Formula auditing group. Click on Error Checking > Reset Ignored errors. In general, messy formulas and a lot of nested formulas create performance hits. Now, if there is some error in one of the initial formula it will be carried through all the nested ones too creating a lot of background work for Excel

Re: Searching for a Solution to a Long-Standing Excel Problem

Most important question, does that happen with a new sheet/file too? Create a new one, add a couple of random formulas and data.

If the file resides on a server try using a local copy to see if the network factor has an impact. Changes in Offices Trust Center could be causing these issues then.

Have you tried copying the contents into a new spreadsheet (unformatted)?
I have a small Excel sheet where I keep track of numbers from a website and unless I clear them from their formatting (via a plain text editor) it will cripple Excel down to not responding for half a minute every time I click something.

I understand that given the size of your sheet this is probably a painful solution but you could at least try it with a partial data set to see if that has any impact – or the other way round, copy portions of your data with their formatting into a new one and see which portion of your sheet creates those performance issues.

Dear all,
I am trying to find a IF function with multiple criteria to get the output as "" (Blank).

I want If function to return "", if its vlookup value is "" or #N/A.
Below is the function i am using, but it still throws #N/A for few cells, while it must return ""

=IF(OR(VLOOKUP(A1,D:E,2,FALSE)="",ISNA(VLOOKUP(A1,D:E,2,FALSE))),"",VLOOKUP(A1,D:E,2,FALSE))

Please find the attachment.

Please correct my small mistake.

Re: VBA to Define new worksheet with custom name and check if it exists

Haan..Got your point...Works cool...Thanks for your fast response..:)
Have a gr8 day..:)

Dear all,
I am trying to define a new sheet with custom name and check if it exits before i do something with code..
Actually in long run i will add this sheet. Before i need to make a cross check if it exists.
But my code throws an error. Please help me fix this error.
Below is my code..

Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
End Function




Sub CheckEntry()
   
    Dim shDate As String
    Dim Tsh As Worksheet                                      ' Worksheet
    
    Application.DisplayAlerts = False
    
    shDate = Format(Date, "dd-mmm-yyyy")    




   Tsh.Name = "Todays" & "(" & shDate & ")"                    ' sheet name




    If Not WorksheetExists(Tsh.Name) Then
        MsgBox "Todays Report not found. Generate Todays Report and Try again", vbExclamation, "Todays Report Not found"
        Exit Sub
        Else




'Do Some opeartion
End sub

Re: Split mutiple dates values in each cell and retain larger value only

Thanks for your code...Works like a charm...And great logic too....:):nono: