Excel trick,find common digit between two number in two different cells

I have 7899 in A1 and 567 in B2

in E1 I am getting the mode 7

the formula is

=MODE(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),VALUE(MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1)))

it's an array formula so you have to use ctrl+shift+enter

Selecting range example in VBA

Sub SelectingLastCellOfContiguousRange()

'Go To last cell

ActiveSheet.Range("a1").End(xlDown).Select

End Sub

Sub SelectingBlankCellOfContiguousRange()

'Go to first blank cell after laste cell

ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select

End Sub

Sub SelectEntireRangeofContiguousCells()

'Select Range Of Cells No Blanks

ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select

ActiveSheet.Range("a1:" & ActiveSheet.Range("a1").End(xlDown).Address).Select

End Sub

Sub SelectEntireRangeofNonContiguousCells()

'Select Range of Cells That Includes Blanks

ActiveSheet.Range("a1", ActiveSheet.Range("a65536").End(xlUp)).Select

ActiveSheet.Range("a1:" & ActiveSheet.Range("a65536").End(xlUp).Address).Select

End Sub

Sub SelectEntire()

'Select Entire Row

Range("1:1").Select

'Select Entire Column

Range("A:A").Select

End Sub

Sub SelectRectangularRange()

'Select Current Region

'ActiveSheet.Range("a1").CurrentRegion.Select

'ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select

'ActiveSheet.Range("a1:" & ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select

'Build Current Region

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select

'Including a blank row

lastCol = ActiveSheet.Range("a1").End(xlToRight).Column

lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row

ActiveSheet.Range("a1:" & ActiveSheet.Cells(lastRow, lastCol).Address).Select

End Sub

Sub SelectMultiNonContColumns()

StartRange = "A1"

EndRange = "C1"

Set a = Range(StartRange, Range(StartRange).End(xlDown))

Set b = Range(EndRange, Range(EndRange).End(xlDown))

Union(a, b).Select

End Sub

Attribute VB_Name = "SelectingLast"

VBA Macro to run linear regression between two variables,VBA Teacher Sourav,Kolkata 08910141720

I have observations for two variable ,the column speed is for X variable,The column distance is for Y variable

Now I have Data Analysis toolpack installed,so this macro will calculate linear regression for X and Y

Sub automatelinearregression()
Sheets("Sheet1").Select

lrA = Cells(Rows.Count, "A").End(xlUp).Row
MsgBox (lrA)

lrC = Cells(Rows.Count, "B").End(xlUp).Row
MsgBox (lrC)
lr = Application.Max(lrA, lrC)  'or Min? I expect the x and y have to have the same number of values?
MsgBox (lr)
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("B1:B" & lr), ActiveSheet.Range("A1:A" & lr), False, True, 95, ActiveSheet.Range("$O$5"), False, False, False, False, , False
End Sub

Result:

Source:http://www.vbaexpress.com/forum/showthread.php?55218-VBA-to-run-a-Linear-Regression-Automatically

Using Spin Button to increase or decrease date in VBA

Private Sub SpinButton1_SpinDown()

Dim datevar As Date
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar - 1)

End Sub

Private Sub SpinButton1_SpinUp()
If Me.TextBox1.Text <> "" Then
On Error Resume Next
 datevar = DateValue(Me.TextBox1.Text)
 Else
 datevar = Date
 End If
 Me.TextBox1.Text = (datevar + 1)
End Sub

Automating Print preview of excel reports using vba

'this is for setting the center header of print which will print as Active Employee List
ersheet.PageSetup.CenterHeader = "Active Employee List"
'this is for setting the right footer of print which will print as Page (number of current page) of Total Pages
ersheet.PageSetup.RightFooter = "Page &P of &N"

'this commented out section works best with portrait printing
'ersheet.PageSetup.Zoom = 60
'ersheet.PageSetup.FitToPagesWide = 1

'ersheet.PageSetup.FitToPagesTall = False

'this section works best for landscape printing
With ersheet.PageSetup
'for setting portrait or landscape
.Orientation = xlLandscape
'these next two line will fit all columns in one page
.FitToPagesWide = 1
.FitToPagesTall = 1
'this line is responsible for continuing one fixed row on several print pages ,it is similar as excel freeze pane
.PrintTitleRows = ersheet.Rows(1).Address
End With

ersheet.PrintPreview

Output

Source:

https://docs.softartisans.com/officewriterwindows/3.0.5/ExcelWriterASP/features/headersandfooters.aspx

https://stackoverflow.com/questions/34052790/vba-code-to-set-print-area-fit-to-1x1-page-and-not-set-print-area-for-certain-t

https://www.youtube.com/watch?v=X4QBS94iNdo&list=PLw8O1w0Hv2zvnLFyiMrihcaOqA0sT0X2U&index=13