Posts by PrasadK
-
-
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 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
Code
Display MoreSub 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
CodePrivate 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
-
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
-
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
Code
Display MoreSub 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
Code
Display MoreSub 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