Practice of for Loops

Sub practice1_loops15feb()
'Code to add a folder name months
MkDir "D:\Study\Excel and VBA Practice\Training Clases\months"
'Code to add 12 workbooks by month names
For x = 1 To 12
name1 = MonthName(x, True)
Workbooks.Add
ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Training Clases\months\" & name1 & ".xlsx"
ActiveWorkbook.Close True

Next x
End Sub


Sub practice2_loops15feb()
'Code to make a list of month in Sheets1
For x = 1 To 12
name1 = MonthName(x, True)
Sheets("Sheet2").Cells(x, 1) = name1
Next x

End Sub

Sub practice3_loops15feb()
'Code to add Sheets
For i = 1 To 9
Sheets.Add after:=Sheets(Sheets.Count)
Next i

'1st Method to rename Sheets
For x = 1 To 12
Sheets("Sheet" & x).Name = Sheets("Sheet2").Cells(x, 1)
Next x

'2nd Method to rename Sheets
For x = 1 To 12
name1 = MonthName(x, True)
Sheets("Sheet" & x).Name = name1
Next x

End Sub


Sub practice4_loops15feb()
'Code to Select range
Dim x As Range
Set x = Sheets("Sheet1").Range("i2:cl156")
'Code to color yellow and blank each cell where value is 1
For Each cell In x
If cell.Value = 1 Then
cell.Interior.Color = vbYellow
cell.Value = ""
End If
Next
'Code to color white and fill 1 each cell where interior color is yellow
For Each cell In x
If cell.Interior.Color = vbYellow Then
cell.Interior.Color = RGB(255, 255, 255)
cell.Value = 1
End If
Next

End Sub

Sub for_dual_loop_list()

'Macro Program to print list
k = 2
For y = 2 To Application.WorksheetFunction.CountA(Sheets("Mar").Range("a:a"))

For x = 1 To Sheets("Mar").Cells(y, 2)
Sheets("Mar").Cells(k, 7) = Cells(y, 1)
Sheets("Mar").Cells(k, 8) = x
k = k + 1

Next x
Next y

End Sub


Sub Old_Program_via_for_loop()

MkDir "D:\Study\Excel and VBA Practice\Training Clases\VBAPractice"
For i = 1 To 3
name1 = MonthName(i, True)
Workbooks.Add
ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Training Clases\VBAPractice\" & name1 & ".xlsx"
Sheets("Sheet1").Range("A1:A20") = name1
Sheets("Sheet1").Range("B1:B20") = Application.WorksheetFunction.RandBetween(1, 31)

ActiveWorkbook.Close True
Next i

Workbooks.Add
ActiveWorkbook.SaveAs "D:\Study\Excel and VBA Practice\Training Clases\months\working.xlsx"
For i = 1 To 3
name1 = MonthName(i, True)
Workbooks.Open "D:\Study\Excel and VBA Practice\Training Clases\VBAPractice\" & name1 & ".xlsx"

For k = 1 To Application.WorksheetFunction.CountA(Workbooks(name1 & ".xlsx").Sheets("Sheet1").Range("A:A"))

With Workbooks("working.xlsx").Sheets("Sheet" & i)
.Cells(k, 1) = Workbooks((name1 & ".xlsx")).Sheets("Sheet1").Cells(k, 1)
.Cells(k, 2) = Workbooks((name1 & ".xlsx")).Sheets("Sheet1").Cells(k, 2)
End With

Next k

Workbooks((name1 & ".xlsx")).Close True
Next i

Workbooks("working.xlsx").Activate
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Report"

For i = 1 To 3
name1 = MonthName(i, True)
Sheets("Sheet" & i).Name = name1
Next i

For i = 1 To 3
name1 = MonthName(i, True)

Workbooks("Working.xlsx").Sheets(name1).Activate
Range("A1").CurrentRegion.Copy
Sheets("Report").Activate
Range("A2000").End(xlUp).PasteSpecial xlPasteValues
'ActiveCell.EntireRow.Delete
Application.DisplayAlerts = False
Sheets(name1).Delete

Next i

End Sub

Comments

Post a Comment

Popular posts from this blog

TDS Calculation System

Image
TDS Calculation System Calculation of how much TDS should deduct from salary will become headache for every employee who will come in the criteria of Tax. In the month of Feb and March employee loosing patient and spent most of the time in internet and communicate with other employee and accountant for better solution to save tax but they do not get appropriate solution. I have designed a nice TDS Calculation System to calculator which will give right and valued information from which the get the scenario of interest and monthly TDS. Download File
Read more

VBA - Additional Controls Dialog Box "MISSING"

VBA - Additional Controls Dialog Box "MISSING" Additional Controls Dialog Box "MISSING" solution, let's do this. 1-click on start menu( in the left on your desk top ) 2-drage a shortcut of Microsoft Excel in your desktop 3-Right click on it and choose Properties then click on compatibility Tab 4-set check on run this program in compatibility mode for 5-Apply then OK Now open Microsoft Excel or (any ms Application like Word, ppt etc), You can see everything is OK and additional Control box is working well This problem usually happen when we change or config our Microsoft to new version( 2003 to 2010 ,.....)
Read more

Loan EMI Calculator Dashboard

Image
Loan EMI Calculator Dashboard Generally the Loan EMI calculation is a headache for every customer when  demanding a sum of money from any bank. The customers search in the internet for better solution but they do not get appropriate solution. We have designed a strong EMI loan calculator which will give right and valued information from which the customers get the scenario of interest and monthly EMI. For example, if any customer is taking 10 Lakh loan at the rate of 10% for the period of 15 years, he/she has to pay Rs 10,746 every month to the bank and if a customer is taking 5 Lakh loan at the rate of 10%, he/she has to pay Rs 5373 every month to the respective institution from where he/she has taken the loan. For Rs 7,50000, the customer has to pay Rs 8059 to the bank at the rate of 10% for the period of 15 years. For the loan of 10 Lakh, the customer has to pay 9,34289 as interest which will build up to 19,34,289 as total in 15 years whereas for the ...
Read more