Posts by venkat1926
-
-
Re: VBA Macro to Insert New row/s based on two dates
I have called your sheet after as "after original"
added a new sheet called "after"the file is attached.
the macro test is in vb editor
and also repeated herewhen you run the macro first it removes data in columns A to c in sheet "after" and then does what you want.The result is in sheet "after"
to retest you can again run the macro test.Code
Display MoreSub test() Dim r As Range, unqB As Range, cunqB As Range, x As String Dim rdata As Range, filt As Range, cfilt As Range, rafter As Range Dim j As Integer, k As Integer, rangeFind As Range, cafter As Range Application.ScreenUpdating = False Worksheets("after").Range("a1:c1").EntireColumn.Cells.Clear Worksheets("before").Activate Set rdata = Range(Range("a1"), Range("C1").End(xlDown)) Set r = Range(Range("B1"), Range("B1").End(xlDown)) Set unqB = Range("A1").End(xlDown).Offset(20, 0) r.AdvancedFilter xlFilterCopy, , unqB, True Set unqB = Range(unqB.Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)) j = Range("G1") - Range("E1") For Each cunqB In unqB x = cunqB With Worksheets("after") Set rafter = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) For k = 1 To j + 1 With rafter.Offset(k - 1, 0) .Value = Worksheets("before").Range("e1") + k - 1 .Offset(0, 1) = x End With Next k End With Next cunqB Worksheets("before").Range("a1:C1").Copy Worksheets("after").Range("a1") With Worksheets("after") Set cafter = .Range("C2") cafter.FormulaArray = _ "=IF(ISNA(INDEX(Before!R2C3:R12C3,(MATCH(1,(Before!R2C1:R12C1=after!RC[-2])*(Before!R2C2:R12C2=after!RC[-1]),0)))),"""",INDEX(Before!R2C3:R12C3,(MATCH(1,(Before!R2C1:R12C1=after!RC[-2])*(Before!R2C2:R12C2=after!RC[-1]),0))))" cafter.Copy Range(cafter.Offset(1, 0), cafter.Offset(0, -1).End(xlDown).Offset(0, 1)) End With Application.CutCopyMode = False Application.ScreenUpdating = True MsgBox "macro done" End Sub
-
Re: Run-time error 13: Mismatch-Stop
debug. successively hit F8
which code statement gives error -
Re: VBA code - for making partial text as "Bold"
suppose data is like this from A1 (KEEP ORIGINAL DATA SAFELY SOMEWHERE (SHEEST 2) SO THAT YOU CAN GET BACK)
aaaaaanotes
bbbbbb
cccnotestry this macro
-
Re: Vlookup Sample
why a macro. if you manually insert the formula of first row in G column and copy down
to copy down fast take the cursor in the firsts row in G column to bottom right of the cell when cursor turns positive sign(+) click this + it will be copied down. as long as data is there in previous column (F)
-
Re: insert row with the datetime value
see attached file. sheet 1 is main data. sheet1 is copied to sheet 2 to preserve data and to run maro "undo"
run the macro "test"
to undo result of macro run "undo"
the macros are also given here
Code
Display MoreSub test() Dim j As Integer, k As Integer, rafter As Range, rbefore As Range, m As Integer Worksheets("sheet1").Activate j = Range("B1").End(xlDown).Row For k = j To 1 Step -1 Set rafter = Range(Cells(k, "B").Offset(1, 0), Cells(k, "B").Offset(7, 0)) 'MsgBox rafter.Address rafter.EntireRow.Insert Range(Cells(k, "A"), Cells(k, "A").Offset(7, 0)).FormulaArray = Cells(k, "A") Range(Cells(k, "c"), Cells(k, "c").Offset(7, 0)).FormulaArray = Cells(k, "C") For m = 1 To 7 Cells(k, "B").Offset(m, 0) = Cells(k, "B") + m / 60 / 24 Next m Range(Cells(k, "A"), Cells(k, "A").End(xlDown)).EntireRow.Cut Cells(k, "A").Offset(3, 0).Select ActiveSheet.Paste 'Set rbefore = Range(Cells(k, "B").Offset(-1, 0), Cells(k, "B").Offset(-3, 0)) 'MsgBox rbefore.Address ' 'rbefore.EntireRow.Insert Range(Cells(k, "A"), Cells(k, "A").End(xlDown)).FormulaArray = Cells(k, "A").End(xlDown) Range(Cells(k, "c"), Cells(k, "c").End(xlDown)).FormulaArray = Cells(k, "C").End(xlDown) For m = 1 To 3 Cells(k, "B").End(xlDown).Offset(-1, 0) = Cells(k, "B").End(xlDown) - 1 / 60 / 24 Next m Next k Columns("A:A").NumberFormat = "m/d/yy;@" Columns("B:B").Select Columns("B:B").NumberFormat = "h:mm:ss;@" End Sub
-
Re: Vba macro code for grouping the data based on 2 hours time...
I was trying to write a macro before your announcement of having had the solution. I agree with royuk. I am also interested in the solution you had
-
Re: sort similar data in rows if the count is greater then or equal to 5.
"if they repeat for more then 5 times" what is meant by "they" do you mean to say host name. on that basis this macro "deletedata" is written
copy sheet1 data to sheet4 to preserve original data and also for undoing macro result.the undo macro is given to get the original data.
Code
Display MoreSub delete_data() Dim hostname As Range, j As Integer, rdata As Range, unqhost As Range, cunqhost As Range, x As String Dim k As Integer Application.ScreenUpdating = False Worksheets("sheet1").Activate Range(Range("a1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete Set rdata = Range("A1").CurrentRegion rdata.Sort Key1:=Range("C1"), Header:=xlYes Set hostname = Range(Range("C1"), Range("C1").End(xlDown)) Set unqhost = Range("A1").End(xlDown).Offset(5, 0) hostname.AdvancedFilter xlFilterCopy, , unqhost, True Set unqhost = Range(unqhost.Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)) For Each cunqhost In unqhost x = cunqhost j = WorksheetFunction.CountIf(rdata, x) If j >= 5 Then GoTo nextstep Else rdata.AutoFilter field:=3, Criteria1:=x 'rdata.SpecialCells(xlCellTypeVisible).EntireRow.Delete rdata.Offset(1, 0).Resize(rdata.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ActiveSheet.AutoFilterMode = False End If nextstep: Next cunqhost Range(Range("a1").End(xlDown).Offset(1, 0), Cells(Rows.Count, "A").End(xlUp)).EntireRow.Delete For k = Range("A1").End(xlDown).Row To 3 Step -1 Cells(k, "C").Select If Cells(k, "C") <> Cells(k - 1, "C") Then Cells(k, "C").EntireRow.Insert Next k Application.ScreenUpdating = True End Sub
Code[CODE]Sub undo() Worksheets("sheet1").Cells.Clear Worksheets("sheet4").Cells.Copy Worksheets("sheet1").Range("A1") End Sub
[/CODE]
-
Re: Populate an arrary based on values in another column
the modified macro
Code
Display MoreSub testone() Dim r As Range, ttype As String With Worksheets("Data") ttype = .Range("B1") End With With Worksheets("list") Set r = .Range("A1").CurrentRegion r.AutoFilter field:=1, Criteria1:=ttype 'r.Columns("B:B").SpecialCells(xlCellTypeVisible).Copy r.Columns("B:B").Offset(1, 0).Resize(r.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy With Worksheets("data") .Range("A4").PasteSpecial End With .AutoFilterMode = False End With Application.CutCopyMode = False End Sub
-
Re: Populate an arrary based on values in another column
not completely understood . anyhow try this macro
Code
Display MoreSub testone() Dim r As Range, ttype As String With Worksheets("Data") ttype = .Range("B1") End With With Worksheets("list") Set r = .Range("A1").CurrentRegion r.AutoFilter field:=1, Criteria1:=ttype r.Columns("B:B").SpecialCells(xlCellTypeVisible).Copy With Worksheets("data") .Range("A4").PasteSpecial End With .AutoFilterMode = False End With Application.CutCopyMode = False End Sub
see whether you get what you wat.
if not give sheet expected. -
Re: Count multiple citeria in a date range
I checked it is ok in my computer. you have entered dates as m/d/yy . this is ok in my computer in some computers it should be entered as d/m/yy. check tisl control panel-region and languages and see how date shd be entered. one other way of testing in an empty column type
=today()
DOES IT GIVE TODAYS DATEyou need not enter the formula (some error may creep in)
select H4. hit function key F2 and enter. (because today() automatically takes todays date)
see whether this helps -
Re: copy cell
will it be possible to give a extract of data (if necessary with non real data) and then explain again
-
Re: Count multiple citeria in a date range
I am attaching the file in which (sheet1) there is text box which is clear.
be careful in entering data.
-
I am fairly familiar with excel and excel vba
I have a sheet with 15 rows.
I am interested in the range of first of 10 rows.dataif I use
specialcells(xlcelltypelastcell) it always give me last cell of sheet and not the selected range (I know there is some other problem in this "xlcelltypelastcelll")how to get the last row of the range except defining
firstrow=2 ( row no. 1 header ows)
rowend=10is there any default procedure.
thanks.
-
Re: VBA Reconcilation Lookup
sorry I noticed only now for e.g. take row no. 4
B4 is
001LBOT13032000
I4 is
00003331
where is commonality.
B4 is not a number but alpha numericI suggest you retink your problem and redesign your questions
beised the foour digits are sany hwere this will give problem
12345678
is not same
2718436789though 1234 are there in both numbers.
-
Re: VBA Reconcilation Lookup
ambigious
cirt 1 col.C is number col. N is number reverse not possible
crit 2 whlich digitis the first four digits or last four digits clairfy -
Re: Divide the Auto fill cell by 1,000,000.00
why do you need a macro. workbooks and sheet1 might have opened already then just in G1 type formula
=B1/(10^6)and enter key.
if you have some other idea revert to newsgroup.
-
Re: Counting unique values based on a criteria
YOU FILE WITH FORMULA IS ATTACHED (SEE J18 AND j19)
-
Re: Error Handler anywhere in excel
i have heard error in a code in a macro which may give error. what is meant by error in workbook with multiple sheets. clarify some more
-