Posts by PrasadK

Once Again Thank You So Much Mumps

Now This Post Thread Has #Solved

i am Really Sorry Mumps for this waiting for a reply

if i want to copy matched value data from another column like C or D

if i enter a value from another column in (inputbox of what are you looking for?) and then i have to get another inputbox to ask which column range name & then i Type C in inputbox then copy all matched data

that why i asked

can you please change the range address to inputbox

Hi Mumps

i need a last help from you

can you please change range (A) into inputbox

here when i run this vba it will ask column range name by inputbox

Thank you so much Mumps it's excellent

Hi Mumps

Thank you for written new code it's working fine

here i need some changes from your vba code

1. after copying data to outputws sheet then all columns should be autofit

2. after copying data to outputws sheet then it will show me MsgBox "Results pasted to " & "(" & OutputWs.Name & ")" & " Sheet"

and select that sheet OutputWs.Select

3. and it's copying data from heading i don't want to copy heading just copy data only i mean avoid heading in data

and if i copying data again from another sheet to same outputws sheet then already have data in that outputws sheet is getting overwritten

Hi Mumps

here i have attached a sample data

Hi Excel Friends

I Need Help From You

I have applied this Below VBA to Copy Matched Data from Entire Row with to Given Sheet Name By InPutBox's here what happening this vba is copying matched data from entire workbook to given sheet name by inputbox successfully

Here what i want this VBA will Copy Matched Data From ActiveSheet only to Given Sheet Name By InPutBox Not from Entireworkbook Match

and one more if i given a sheet name by inputbox and the sheet is not exist in workbook then it will ask me by msgbox sheet not found do you want to create a sheet by given name then click yes

here i getting run time error when i type sheet name by inputbox and the sheet is not exist in workbook

Sub SearchAll()
    
    Dim ws As Worksheet, OutputWs As Worksheet
    Dim rFound As Range, IsValueFound As Boolean, IsValueNotFound As Boolean
    Dim strName As String
    Dim count As Long, LastRow As Long
    
    strName = Trim(InputBox("What are you looking for?"))
    If strName = "" Then Exit Sub
    
    Set OutputWs = Worksheets(InputBox("Enter Sheet Name"))
    LastRow = OutputWs.Cells(Rows.count, "A").End(xlUp).row
    
    For Each ws In Worksheets
        If ws.Name <> OutputWs.Name Then
            Debug.Print "Checking " & ws.Name
            Set rFound = FindAll(ws.UsedRange, strName)
            If Not rFound Is Nothing Then
                Set rFound = rFound.EntireRow
                count = rFound.Cells.count / Columns.count 'how many matched rows?
                Debug.Print "Found " & count & " rows"
                rFound.Copy OutputWs.Cells(LastRow + 1, 1)
                LastRow = LastRow + count
                IsValueFound = True
                IsValueNotFound = True
            End If
        End If
        
    Next ws
    
    If IsValueFound Then
       OutputWs.Select
       MsgBox "Results pasted to " & "(" & OutputWs.Name & ")" & " Sheet"
    Else
    If IsValueNotFound Then
       OutputWs = MsgBox("Sheet " & OutputWs.Name & " Not Found in WorkBook Do you want Create a New Sheet with Given Name Then Click Yes", vbQuestion + vbYesNo)
       If OutputWs = vbYes Then
       Worksheets.Add OutputWs.Name
    End If
  End If
  End If
 End Sub


Public Function FindAll(rng As Range, val As String) As Range
    Dim rv As Range, f As Range
    Dim addr As String
 
    Set f = rng.Find(what:=val, After:=rng.Cells(rng.Cells.count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()
    
    Do Until f Is Nothing
        If rv Is Nothing Then
            Set rv = f
        Else
            Set rv = Application.Union(rv, f)
        End If
        Set f = rng.FindNext(After:=f)
        If f.Address() = addr Then Exit Do
    Loop
    Set FindAll = rv
End Function

Thank you so much @6StringJazzer

Hi Excel Expert Friends

I need a small help from you

Here what I want I am doing data entry From Col-A to Col-D here I want changes in this vba code here if type any value from col-A to col-D then only cursor will move to next cell

Like if i enter any value on Col-A and Press Enter then move cursor to next cell of Col-B like this for every column from A to D

If I don’t enter any value on Col-A and Press Enter then a pop-up MsgBox will be show me a message like this

MsgBox(You Can’t Leave Cell Blank Please Enter any Value to Move to Next Cell)

Like this I want for every Column From A to D

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then Range("B" & Target.Row).Select
    If Target.Column = 2 Then Range("C" & Target.Row).Select
    If Target.Column = 3 Then Range("D" & Target.Row).Select
    If Target.Column = 4 Then Range("A" & Target.Row + 1).Select
End Sub

i have applied code in excel 2007 version

I am using excel 2007

why i have added on error resume next

When i have applied this code into worksheet change and i went to sheet and enter a mobile number in a cell

Then i get run time error

Thank you so much royUK

it's working perfectly

i have applied this code like this it's working thank you so much

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
 Const s As String = "+91-"
 If Len(Target) = 10 Then Target.Value = s & Target.Value
End Sub

Can you change this code into worksheet change event

i have applied this macro to add +91- before mobile number of 10 digits

here can any one change this code into worksheet event change macro because

when i type mobile number in a cell +91- should be add automatically

here i know we can add +91- in cell range by changing cell range formating into format cells

in format cells we have apply custom format to add +91- before mobile number

here i want to add +91- before mobile number in general format only

Sub Add()
For Each r In Range("A1:A4")
    If Not (Left(r.Value, 2) = "+91-") Then
        r.Value = "+91-" & r.Value
    End If
Next r
End Sub

Thank you so much rollis13

i have done all correctly it's not unprotecting

when i am run this macro it's protecting whole active sheet and again run again macro it's don't unprotecting i want to protect and unprotect just a in range A1 to D20

Sub lockcells()
 Dim Rng As Range
 
 On Error GoTo err_handler
 Set Rng = Range("A1:D20").SpecialCells(xlCellTypeConstants)
 ActiveSheet.Unprotect Password:="123"
 Rng.Cells.Locked = True
 Rng.FormulaHidden = False
err_handler:
 ActiveSheet.Protect Password:="123", UserInterFaceOnly:=True
End Sub

any one answer this

when i am running this macro to protect and unprotect in given range it is protecting as well and when i am again running this macro to unprotect it's not unprotecting

Sub lockcells()
    Dim Rng
    Dim MyCell
    Set Rng = Range("A1:D20")
    For Each MyCell In Rng
        If MyCell.Value = "" Then

        Else: ActiveSheet.UnProtect Password:="123"
            MyCell.Locked = True
            MyCell.FormulaHidden = False
            ActiveSheet.Protect Password:="123", UserInterFaceOnly:=True
        End If
    Next
End Sub