Sir I have created usefrom if that is possible then please make an example i would really appreciate your help thanks.
Posts by Excel&VBA
-
-
Thank you so much make it correct end if
-
-
Hi,
I have searched and google to know that how we can make custom search userform for filteration. I have attached a sheet where added a button which filter the values that is >100 and <5000.
All i want is to do that thing via custom button when i click on it it will ask which values you want to filter both greater and less.
Any help will be highly appreciated
-
rory,
Sir thank you so much for the help. Thank you.
-
I have make this code to sort the excel columns and i added the sheet name as well.
But the issue is when i run this code by opening different sheet it gives the error and on the sheet where data is available it works and doesn't give error.
can this code be short and easy.
Thanks.
CodeSub sor123() ActiveWorkbook.Worksheets("Sheet1").Range("C2:C1000000", Range("C2").End(xlDown)).Sort Key1:=Range("C2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("D2:D1000000", Range("D2").End(xlDown)).Sort Key1:=Range("D2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("E2:E1000000", Range("E2").End(xlDown)).Sort Key1:=Range("E2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("H2:H1000000", Range("H2").End(xlDown)).Sort Key1:=Range("H2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("I2:I1000000", Range("I2").End(xlDown)).Sort Key1:=Range("I2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("T2:T1000000", Range("T2").End(xlDown)).Sort Key1:=Range("T2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("U2:U1000000", Range("U2").End(xlDown)).Sort Key1:=Range("U2"), Order1:=xlDescending, Header:=xlNo ActiveWorkbook.Worksheets("Sheet1").Range("W2:W1000000", Range("W2").End(xlDown)).Sort Key1:=Range("W2"), Order1:=xlDescending, Header:=xlNo End Sub
-
-
I was using this code which was matching the values by three months such as
Aug,Sep,Oct then give the result in Col"R" where at Col"Q" , "D" is available
same for next three months
sep,Oct,Nov code will give the result in Col"R" where at Col"Q" , "D" is available
Now i have made the calculation's manually by using Vlookup i found a wrong statements
i need a Solution that is
If three months has same sku then it will be as "Remove" if sku is not available even in one month it will be "empty".
any help would be highly appreciated.
Code
Display MoreSub Matching() Dim row1, month3, month2, month1, Rating3, Rating2, Rating1, sku As String Dim ws As Worksheet Dim xa, xb, xc As Integer xa = 1 xc = Worksheets("Main").Cells(1, 5) Sheets("Main").Range("A:A").Clear For Each ws In Worksheets Sheets("Main").Cells(xa, 1) = ws.Name xa = xa + 1 Next ws Sheets("Main").Range("A2:C8").Sort Key1:=Sheets("Main").Range("B2"), Order1:=xlAscending, Header:=xlNo For xb = 4 To xc month3 = Worksheets("Main").Cells(xb, 3) month2 = Worksheets("Main").Cells(xb - 1, 3) month1 = Worksheets("Main").Cells(xb - 2, 3) For x = 2 To 800 If Worksheets(month3).Cells(x, 17) = "D" Then sku = Worksheets(month3).Cells(x, 2).Text With Sheets(month3) Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues) If Findrow Is Nothing Then Rating3 = 0 Else row1 = Findrow.Row If Worksheets(month3).Cells(Findrow.Row, 17).Text = "D" Then Rating3 = 1 Else End If End If End With With Sheets(month2) Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues) If Findrow Is Nothing Then Rating3 = 0 Else If Worksheets(month2).Cells(Findrow.Row, 17).Text = "D" Then Rating2 = 1 Else End If End If End With With Sheets(month1) Set Findrow = .Range("B:B").Find(What:=sku, LookIn:=xlValues) If Findrow Is Nothing Then Rating1 = 0 Else If Worksheets(month1).Cells(Findrow.Row, 17).Text = "D" Then Rating1 = 1 Else Rating1 = 0 End If End If End With If Rating1 = 1 And Rating2 = 1 And Rating3 = 1 Then Worksheets(month3).Cells(row1, 18) = "Remove" Else End If Else End If Next x Next xb MsgBox ("Worksheets Updated") End Sub
-
Sir, thank you so much for the help.
-
Sir exactly
Formula is not working on D8:D19 because there is no H1-2021 value in Col "A".
If such condition is happen then formula should consider Q1-2021 and Q2-2021 as H1-2021 for accurate calculation as "D20" has.
-
I have been trying to correct the mistake but i am unable to understand the issue that, it is working for all cell instead of 12 that are highlited.
can someone please make it correct or share an accurate formula to use.
any help will be highly appreciated.
-
thank you so much for the help.
-
Thank you so much if i want to paste the data as transpose how i can add this.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True
-
apologies that i have not uploaded the sheets. But now i have uploaded the sheets which accurate data. to copy paste the data on a condition
Sheet1 has data which is available in row 4 and 5, where code will fetch that in row 4 has word "0f3" then code will copy the exactly data available under word "0f3" to end of the data (Data of row 5 will be copied) and paste into sheet2 range J10 to further...
The code will not copy the data which is before the word "0f3" that is highlighted."
I have pasted the required data into Sheet2 which is required from Sheet1.
-
Copying Data from Workbooks("Sheet1").Worksheets("Sheet1") Range("A5:BBC5") if that row has a word "king" it should copy the data of complete row and paste it to Workbooks("Sheet2").Worksheets("Sheet2") Range("K10") that sheet.
I am unable to figure it out that why my code is not working.
any help would be highly appreciated.
Code
Display MoreSub CopyRows() Dim rightend As Integer Dim x As Integer Dim c As Range Dim Source As Worksheet Dim Target As Worksheet Set Source = Workbooks("Sheet1").Worksheets("Sheet1") Set Target = Workbooks("Sheet2").Worksheets("Sheet2") rightend = Source.Range("L" & Columns.Count).End(xlRight).Columns: x = 1 For Each c In Source.Range("A5:BBC5" & rightend) If c.Value = "king" Then c.EntireRow.Copy Worksheets("sheet2").Range("A" & x) x = x + 1 End If Next c Targte.Range("K10").Select ActiveSheet.Paste End Sub
-
Thank you its working.
-
I was trying to create a Vlookup formula that where 2 conditions are met then it should give the accurate value.
-
Yes it is exactly working as i requested Thanks.
-
Thank you so much i deleted that line.
-
Thank you so much. Its working but i have also requested to select a folder via Code rather than pasting link in code.