PDA

View Full Version : Excel Macro to populate the values in the required cells


pbabu
10-30-2013, 08:55 PM
Hi All,

I have come up with a generic excel sheet which has n number of sheets where i need to populate a set of values where the listid column ends.

if sheet name ends with "P" letter than need to populate f,g,h and i columns with the following values" 1 Minutes -4431 01/01/2013"
if sheet name ends with "S" letter than need to populate f,g,h and i columns with the following values" 0 Minutes -4431 01/01/2013"


sample sheet enclosed here for your ready reference.would appriciate if you can help me to automate this.thank you for your assitance.

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdsh r6 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg.9iEktVkTAHk9iF9_pdsh r6)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ- (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9z XI (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg.9iDVgy6wzct9iFBxma9z XI)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_ 3f (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg.9iDQN7TORHv9iFGQQ5z_ 3f)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11 nH (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg.9iDLC2uEPRW9iFGvgk11 nH)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOe Xa (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg.9iH3wvUZj3n9iHnpOxOe Xa)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpT G8 (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg.9iGReNGzP4v9iHoeaCpT G8)
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I (https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
patel
10-30-2013, 11:30 PM
Sub a()
For Each sh In Sheets
LR = sh.Cells(Rows.Count, "A").End(xlUp).Row
sh.Range("H9:H" & LR) = "Minutes"
sh.Range("I9:I" & LR) = -4431
If Right(sh.Name, 1) = "S" Then sh.Range("G9:G" & LR) = 0
If Right(sh.Name, 1) = "P" Then sh.Range("G9:G" & LR) = 1
Next
End Sub
Excel Fox
10-30-2013, 11:40 PM
Try this from a module or workbook code module




Sub ExcelFox()

Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
If Right(UCase(wks.Name), 1) = "P" Then
With wks
.Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
Array(1, "Minutes", -4431, #1/1/2013#)
End With
ElseIf Right(UCase(wks.Name), 1) = "S" Then
With wks
.Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
Array(0, "Minutes", -4431, #1/1/2013#)
End With
End If
Next wks
End Sub
Rick Rothstein
11-02-2013, 12:08 PM
Try this from a module or workbook code module



Sub ExcelFox()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If Right(UCase(wks.Name), 1) = "P" Then
With wks
.Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
Array(1, "Minutes", -4431, #1/1/2013#)
End With
ElseIf Right(UCase(wks.Name), 1) = "S" Then
With wks
.Range("G9:J" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value = _
Array(0, "Minutes", -4431, #1/1/2013#)
End With
End If
Next wks
End Sub

You can shorten the above code to this...


Sub ExcelFox()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.Name Like "*[PS]" Then wks.Range("G9:J" & wks.Cells(Rows.Count, "A").End( _
xlUp).Row) = Array(Abs(wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
Next
End Sub


Note: I wrapped the long line of code using the line continuation character for neatness of display, but you can unwrap that line if you want.
pbabu
11-03-2013, 10:46 PM
Many thanks for your quick reply.
great guys , this is perfectly working. would it be possible to generate a sequence set of values with respect to "P" sheets which means if 1st (P) sheet has a value of "1" then next "P" sheet should contain a value of "2" i,e adding +1 to the next occurrence.

For example :

P Sheet " 1 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"
P Sheet " 2 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"

Please note there is no change required for "S" sheets.
Rick Rothstein
11-04-2013, 12:03 AM
Many thanks for your quick reply.
great guys , this is perfectly working. would it be possible to generate a sequence set of values with respect to "P" sheets which means if 1st (P) sheet has a value of "1" then next "P" sheet should contain a value of "2" i,e adding +1 to the next occurrence.

For example :

P Sheet " 1 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"
P Sheet " 2 Minutes -4431 01/01/2013"
S Sheet " 0 Minutes -4431 01/01/2013"

Please note there is no change required for "S" sheets.
Does this code do what you want?


Sub ExcelFoxAndRick()
Dim wks As Worksheet, Rng As Range
For Each wks In ThisWorkbook.Worksheets
Set Rng = wks.Range("G9:J" & wks.Cells(Rows.Count, "A").End(xlUp).Row)
If wks.Name Like "*[PS]" Then Rng = Array(Abs(wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
If Right(wks.Name, 1) = "P" Then Rng.Columns(1) = Evaluate("ROW(" & Rng.Address & ")")
Next
End Sub
pbabu
11-04-2013, 12:45 PM
Apologies for the confusion.No Rick, the logic that you have provided is basically generating a sequence set of values with in the same sheet but i need other way around which means complete (P) sheet should have same set of value like ("1 Minutes -4431 01/01/2013")
and next occurence of (P) should have incremental value like (" 2 Minutes -4431 01/01/2013).

For example:
FreeP sheet : " 1 Minutes -4431 01/01/2013".
FreeS Sheet : " 0 Minutes -4431 01/01/2013"
CostP Sheet : "" 2 Minutes -4431 01/01/2013"
CostS Sheet : " 0 Minutes -4431 01/01/2013"

I hope this clears the confusion.
Rick Rothstein
11-04-2013, 07:51 PM
Apologies for the confusion.No Rick, the logic that you have provided is basically generating a sequence set of values with in the same sheet but i need other way around which means complete (P) sheet should have same set of value like ("1 Minutes -4431 01/01/2013")
and next occurence of (P) should have incremental value like (" 2 Minutes -4431 01/01/2013).

For example:
FreeP sheet : " 1 Minutes -4431 01/01/2013".
FreeS Sheet : " 0 Minutes -4431 01/01/2013"
CostP Sheet : "" 2 Minutes -4431 01/01/2013"
CostS Sheet : " 0 Minutes -4431 01/01/2013"

Okay then, how about this macro, does it do what you want?


Sub ExcelFoxAndRick()
Dim Index As Long, Wks As Worksheet, Rng As Range
For Each Wks In ThisWorkbook.Worksheets
Set Rng = Wks.Range("G9:J" & Wks.Cells(Rows.Count, "A").End(xlUp).Row)
If Wks.Name Like "*[PS]" Then Rng = Array(Abs(Wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
If Right(Wks.Name, 1) = "P" Then
Index = Index + 1
Rng.Columns(1) = Index
End If
Next
End Sub
pbabu
11-04-2013, 11:03 PM
it works perfect. Thank you very much rick !
pbabu
11-20-2013, 11:42 PM
It was very useful rick , would it be possible to avoid hard coding of some values which means rather than hard-coding of "-4431,1/1/2013" can we pick up B1 and B2 values to populate them
for example -4431 can be copied from B1 cell and 1/1/2013 can be copied from B2 Cell.
Adam991
11-21-2013, 04:41 PM
You can shorten the above code to this...


Sub ExcelFox()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.Name Like "*[PS]" Then wks.Range("G9:J" & wks.Cells(Rows.Count, "A").End( _
xlUp).Row) = Array(Abs(wks.Name Like "*P"), "Minutes", -4431, #1/1/2013#)
Next
End Sub


Note: I wrapped the long line of code using the line continuation character for neatness of display, but you can unwrap that line if you want.


Hi Rick Rothstein

I am having problem here ["G9:J" & wks.Cells(Rows.Count, "A")], I was just trying to do it for my practice but it is not giving me results, can you elaborate please?