Excel VBA – A general how to
A Pivot Table should do it for you!!
vba
VBA code takes long time to execute in excel
The first thing I do to speed up VBA like this is to use Screen Updating Sub LOOK() Application.ScreenUpdating = False Worksheets(“Input”).Unprotect (“ds12345”) Dim found As Range Set found = Sheets(“Records”).Columns(“D”).Find(What:=ActiveSheet.Cells(3, 13).Value, SearchDirection:=xlPrevious, LookIn:=xlValues, LookAt:=xlWhole) If found Is Nothing Then MsgBox “Not found” Else MsgBox “Found on row ” & found.Row End If Dim iRow … Read more
VBA – How to grab the cell below if a cell has text?
In general, this is really a not a great question, as it fails the rules of StackOverflow, but still, this is some possible answer, producing this output: Public Sub TestMe() Dim myCell As Range Dim currentCell As Range: Set currentCell = Range(“D1”) Dim rangeToWrite As Range: Set rangeToWrite = Columns(“D:E”) Dim lastRow As Long: lastRow … Read more
Rearranging Excel Cell based on Value [closed]
This sub procedure works with two variant arrays. Option Explicit Sub Macro3() Dim i As Long, j As Long, nr As Long Dim tmp As Variant, arr As Variant, hdr As Variant, vals As Variant With Worksheets(“sheet4”) tmp = .Cells(1, “A”).CurrentRegion ReDim vals(LBound(tmp, 1) To UBound(tmp, 1), LBound(tmp, 2) To UBound(tmp, 2)) nr = UBound(tmp, … Read more
to make this code simpler
Something like this: Sub Macro1() Dim Sh1 As WorkSheet, Sh2 As WorkSheet Set Sh1 = Sheets(“Sheet1”) Set Sh2 = Sheets(“ALB1”) Dim R As Long For R = 2 to 127 Sh1.Range(“D” & R & “:E” & R).Copy Sh2.Range(“C” & R – 1) Next R End Sub Or even better: Sheets(“ALB1”).Range(“C1:D126”) = “=Sheet1!D2” Assigning a formula … Read more
How do I get values from a hidden worksheet using VBA [closed]
As per your comment you want to loop through your range then try the below. This is just a for loop in VBA sub testcode() dim i as double dim yournametofind as string for i = 2 to 20 yournametofind = Worksheets(“DataValues”).Range(“A” & i).value if yournametofind = yournametofind then ‘do your things else do your … Read more
Duplicate number and it value in column EXCEL [closed]
Option Explicit Sub wqewrty() With Worksheets(“sheet1″).Cells(1, 1).CurrentRegion .Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _ Key2:=.Columns(2), Order2:=xlAscending, _ Orientation:=xlTopToBottom, Header:=xlNo With .Columns(1).Offset(1, 0) .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=”=$A2=$A1” .FormatConditions(1).NumberFormat = “;;;” End With End With End Sub I’ve assumed that you wanted to use column B as a secondary sort key to the primary sort key on column A. If … Read more
How to find the days difference between two dates [closed]
Instead of using vba just enter the formula for the first row on C1 : =ROUND(a1,0)-ROUND(b1,0) Then just the formula to the end of exisiting rows. If you insist using vba code use the simple code below: Dim LastRow As Long LastRow = ActiveSheet.Range(“A” & Rows.Count).End(xlUp).Row Range(“c1”).Select ActiveCell.FormulaR1C1 = “=ROUND(RC[-1],0)-ROUND(RC[-2],0)” Range(“c1”).AutoFill Destination:=Range(“C1:C” & LastRow) End … Read more
using for loop for vba
Can you please try this? Idea would be something like this. Dim FileNo As String For i = 1 To 35 FileNo = “File” & i Windows(FileNo).Activate ‘Rest of the code Next