Posts by samuels90

Re: Alternative To Multiple Loops

Andy,

Thank you. I am sure we are going in a more efficient direction.

I appreciate your help.

cheers!

Re: Alternative To Multiple Loops

Andy,

I am interested in changing the number of rows, in the macro you provided, that would be the "29". By the way, thanx for the catch on month 8.

Cheers

Re: Alternative To Multiple Loops

Andy, Dave:

Thank you for your responses.

Dave, I will yield your advice.

Andy, I love the brevity of the code you provided me. Do you know of a methodology for VB to count the number of rows and then input into the code you gave me?

Cheers

Hey everyone,

I have written some code (see below) which appears clumsy, can anyone suggest a more efficient approach?

Thank you for your response.

samuels90

Sub codelabel()
Dim check As Integer


Do While check < 29
ActiveCell.Value = "'01"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 58
ActiveCell.Value = "'02"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 87
ActiveCell.Value = "'03"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 116
ActiveCell.Value = "'04"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 145
ActiveCell.Value = "'05"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 174
ActiveCell.Value = "'06"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 203
ActiveCell.Value = "'07"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 261
ActiveCell.Value = "'08"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 290
ActiveCell.Value = "'09"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 319
ActiveCell.Value = "'10"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 348
ActiveCell.Value = "'11"
ActiveCell.Offset(1, 0).Select
check = check + 1
Loop
Do While check < 377
ActiveCell.Value = "'12"
ActiveCell.Offset(1, 0).Select
check = check + 1
End Sub

Hey everyone,

I have a range of cells that is 10 rows high. I want to copy and paste this range 11 times while identifying each of the 11 copies of the range, such as; 02,03,04...12.

Please see attachment for an example of what I am trying to do.

Does anyone have an idea of how this might be done in VB?

Thank you in advance.

samuels90

Re: Name New Sheet Used For PivotTable

Dave,

Thank you very much that did!

samuels90

Hey everyone,

In the macro I am writing I open a pivot table which as you know creates a sheet. I would like to name that sheet. My problem is that the default name(number if you will) changes every time I run my macro so I am unable to define which sheet I want to give a name.

Thank you in advance for feedback.

Re: Declaring a date range as a Variable

Richard,

Yes, I will have a longer list. Fuel prices in my industry changes every week. My goal is to write a program that determine the fuel cost each time a vehicle starts a job. A vehicle fills up everytime it starts a job, and the fuel cost will be different based on the location and supplier. However, the constant is that all suppliers will offer an agreed rate for 1 week.

There are other complications, but I am building the program one step at a time (i am a novice). For example, my database will also include different suppliers who will sell at different prices within the same period.

Please be candid if my explanation is not transparent.

Thank you for your help.

Don

Re: Declaring a date range as a Variable

Richard,

Thank you very much for your response.

I have had a similar thought, but being a rather inept VB programmer I felt that I would run into more complications than I am capable to manage. Attached is my code and sample sheet.

Sub FuelCostInter()
Dim Week1(1 To 2) As Date
Dim Price1 As Currency
Dim Check As Integer


 
Week1(1) = #8/31/2007#
Week1(2) = #9/5/2007#


Price1 = Range("B4").Value


Do While Check < 12
If ActiveCell.Value = Week1 Then
   ActiveCell.Offset(0, 1).Value = Price1
   ActiveCell.Offset(1, 0).Select
Else
    ActiveCell.Offset(1, 0).Select
End If
Check = Check + 1
Loop
End Sub

Identify Cells Between a Date Range

Hello everyone,

I am writing a program and I want to write.

Dim Week1 as Date 


Week1 = #8/31/2007# to #9/5/2007#

Will someone let me know whether this is possible and if so, how is it written?

Thank you.

Re: Select Case: Assigning Cost Based On The Date Range.

Reafidy,

Thank you for the Forum Rules and Anatomy of A Good Thread Title, and for showing me how to present my codes.

Is there an alternate way to write my code? I want to pick up the Week1 rate (2.2) if the date column falls within the define date range.

Thank you again for your kindness.

Re: Select Case: Assigning Cost Based On The Date Range.

Turtle44,

Thank you very much for your response.

From my perspective the question involves using Select Case - particularly, how the statement should be written when I am trying to define a date range.

I am new at learning to using VB and do not always know what would be most efficient. However, I consider if I had 30,000 lines of data, using VB would be more efficient and use less memory.

Code tags - I need to learn how to use the code tags.

I know what dates falls within Week1, I am trying to determine why my program does not work the way I coded it.

Thank you for any further point of view you may have.

I want to select the cost for fuel based on the date using the select case method.

Thank you very much for your perspective.

Sub FuelCostApplication()
Dim Check As Integer
Do While Check < 10
Select Case Range("B4").Value
Case 8 / 30 / 2007 To 9 / 5 / 2007:
Range("C10").Value = Range("B4").Value
ActiveCell.Offset(1, 0).Select
Case Else
Range("C10").Value = 0
End Select
Check = Check + 1
Loop
End Sub

[ss=
-----------A----------- ---B---- --C-- --D-- --E-- F
1 US Air - Ground Expense
2 Fuel
3 Week1 Week2 Week3 Week4
4 Shell 2.2 2.34 2.48 2.62
5 Pacific 2.12 2.33 2.54 2.75
6 World Fuel Service 2.23 2.46 2.69 2.92
7 6.55 7.13 7.71 8.29
8
9
10 30/08/07 0
11 31/08/07
12 1/09/07
13 2/09/07
14 3/09/07
15 4/09/07
16 5/09/07
]*[/ss]

You may need to see attached.

Re: Declaring Variables

Mate,

Do I need an Else argument in this code for those situations where "A" is not = ""?

Thank you.

Re: Declaring Variables

Thanx a lot mate!

Will someone please assist me to complete this coding?

Sub DeleteBlankRowInColumnA()
'this macro scrolls down Column A if the cell is empty the row is deleted.
Dim A As Integer
A = 0
Do Until A = 142
If Range(0, "a1") = 0 Then
Selection.Delete shift:=xlUp
End If
Loop


End Sub

Re: Methodology For Average

Thank you for your help.

Hello All,

I have a Cumulative Percentage Table with which I would like to average the percentage for months 1-12. I currently sum the columns & divide by the number of months of activity but the numbers do not make sense. Can someone suggest a more robust approach.

Thank you.

Re: Vlookup comparison

Thanx to Thomach & Batman for your help.

Cheers!

I would like to do a lookup for a location (C23) if the date is after 7/15/06 (J23). I am not sure how the syntax for the date should be in the function.

See example below:
=IF(J23>=38913,VLOOKUP(C23,'Suppliers_Location Lookup'!$A$3:$B$150,2,FALSE))

Thank you for your help.