Efficient Loop Code To Format Labels

Feb 18, 2009

I am trying to creat a code that with take the value of an active cell and depending on this value will assign a backcolour to a label corresponding to the cell. so far so good.
I then want the code to offset to the next cell in the range read its value and assign a colour to that cells backcolor.

here is an idea.

range("A1").select
for n = 1 to 4
if activecell = "A" then
Label1.BackColor = RGB(0, 0, 0)
else
if activecell = "B" then
Label1.BackColor = RGB(0, 0, 255)
else
if activecell = "C" then
Label1.BackColor = RGB(0, 255, 0)
end if
activecell.offset(0,1).select
next n

Firstly I would like the next loop (refering to A2 in this example) to refer to Label2 not Label1 and so on.
secondly the example would loop through 4 cells in one row (A1:A4) but I would like the code to apply to several rows ie (A1:D4).

so thats 16 cell and 16 labels. I could code this in a very inefficient way but I am sure ther is a simple method.

View 9 Replies


ADVERTISEMENT

Code For Efficient Format

Feb 23, 2007

Was hoping to get some help with a mini project of mine.. Currently I am looking to create a progress/commission report (pull data from an access dbase) and populating an excel sheet. I can manage to pull in the data fine its just gettting into a format that is the complicated thing. I was hoping that someone might have a few moment to pick thru my code (see attached) and maybe suggest an alternative/more efficent way to do the refreshing.

View 5 Replies View Related

Is A Do Loop Efficient

May 25, 2007

I have a question about loops. I've read that loops are slow and there are better ways to achieve the same results but I do not know how to accomplish the same without using a loop. I've attached a code that looks in the first sheet and if in column "f" if criteria is met it will copy the row to a separate sheet. Then is goes to a separate sheet and does the same thing after it skips a row then copy header information. What i'm doing is consolidating multiple sheets to one for prioritizing data. The loop works but I've been going back and trying to make my workbook a little more effecient and really cannot figure out how I would do it differently.

Sub AVAILABLEBILLETS()

Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Dim j As Integer
Dim I As Integer
Dim K As Integer
Dim fillcell As String

View 9 Replies View Related

More Efficient Alternative To Find-Replace Loop?

Feb 1, 2010

I was tasked with looking through a 10,000 row by 20 column spreadsheet for 628 different ID Codes and replacing them with their Descriptions. The ID Codes could appear individually in any of these cells (200,000 cells!).

The list of ID's and Descriptions hardly ever changes, so I decided to create the two-dimensional array as part of the find-replace macro shown below (only a few lines of each dimension are shown).

My question isn't related to that (although if you can suggest a better alternative please do). The main "work" of the macro is the loop at the end of the array declarations, which essentially loops through all 628 ID codes in array dimension 1 and does a Find-ReplaceAll with its corresponding Description from array dimesion 2.

This task, manually, could take days. My macro has whittled the task down to 2.5 minutes on a 5-year-old laptop, but I was hoping one of the gurus might suggest an even better method than 628 loop iterations. If not, so be it, the end users will appreciate what I've done and then have to find something to do with their "free time."

View 5 Replies View Related

Efficient Way To Find All Cells With Particular Format

Feb 8, 2013

I have a lot of data with the wrong number format. I want to find all cells with this particular number format and change it to another.

I know I can do this by looping through all the cells on the sheet or by using the find method, but both ways require looping. I am hoping there is a quicker way. I originally thought there was a SpecialCells type that was format conditional (not conditional formatting), but there doesn't seem to be one.

View 8 Replies View Related

Efficient VBA Code Vs Macro Recorder Code

Sep 9, 2007

What is the most efficient VBA code for the following macro recorded codes? I wish to write more efficient code versus the lengthy, cumbersome macro recorder code.

1) Macro Recorder Code to Copy One Sheet to Another

Sheets("Data Apple").Select
Cells.Select
Selection.Copy
Sheets("Banana").Select
Cells.Select
ActiveSheet.Paste

2) Macro Recorder Code for Replacement Purposes......................

View 9 Replies View Related

Making Code Efficient

Dec 29, 2009

following code be write down in more efficient manner :>>>

Range("B3") = Range("L" & Target.Row)
Range("C3") = Range("O" & Target.Row)
Range("D3") = Range("Q" & Target.Row)
Range("E3") = Range("R" & Target.Row)

View 9 Replies View Related

Efficient Code To Map Cells From One File To Another

Oct 21, 2006

Is there a way to make the code below more efficient?

My data resides in "Buildings August 2006(2).xls " and I wish to copy some cells to "Backlog Analysis .xls"

'Copy J16 to B5
Windows("Buildings August 2006 (2).xls").Activate
Range("J16").Copy
Windows("Backlog Analysis.xls").Activate
Range("B5").PasteSpecial

I repeat the code above 7 more times to handle J29-B6, J33-B7, J42-B8, L56-B10, L57-B11, L60-B13 and L62-B14.

Is there an Array I can use to encompass all of the copy and paste commands?

This is step 1 of something I am doing for work

Step 2 will be to figure out how to work my way through all of the worksheets in "Buildings August 2006(2). I have seen numerous code examples for this so I think it shouldn't be too hard.

View 5 Replies View Related

Trying To Move My VBA Programming To The Next Level And Use More Efficient Code

Oct 18, 2007

I'm trying to move my VBA programming to the next level and use more efficient code.

I'm wondering if there is a faster way to run the loop below, perhaps removing the 'For c = 2 to LastRow' as it seems a little clunky to me.

For c = 2 to LastRow

If Cells(c, Range("Product_Type").Column) = "" Then _
Cells(c, Range("Product_Type").Column).Interior.ColorIndex = 41

Next c
I've seen a similar thing somewhere where it was all done in one statement without the loop.

View 9 Replies View Related

Condense Code To Make More Efficient And Run Until Blank Cell

Apr 10, 2014

Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.

[Code] ......

View 2 Replies View Related

Format Userform Labels

Aug 25, 2006

Is there a method for formating numbers in a label on a userform?

View 7 Replies View Related

Conditionally Format Axis Labels On Graphs

Apr 2, 2013

I am putting together a graph and my manager wants the companies with a zero value in the bar chart to stand out (0 = really good). The only thing I could think of was making the individual x-axis label's font turn green if value was zero. Is there any way to accomplish this? (Additional: This chart will be updated every month with new data so I do not want to change by hand every month).

View 4 Replies View Related

VBA For Changing Number Format Of Chart Labels?

Oct 10, 2013

This should be pretty simple but I don't know how to do it. When I run the macro recorder, it doesnt record the change. I just want to change the number format of the chart labels from currency to a normal number and visa-versa. Why doesnt this work?

Code:
ActiveSheet.ChartObjects("chart 5").SeriesCollection(1).DataLabels.NumberFormat = "[$-809]#,##0.00;-[$-809]#,##0.00"

View 2 Replies View Related

Format Data Series Point Labels

Jul 1, 2007

I am building stacked XY Scatter charts and having difficulty formatting the y-axis values (which are data series point labels)

Rob Bovey's XY Chart Labeler manages this fine, but I work in a strict corporate environment which does not allow downloads. Also the file will be posted on our web as a tool for all company users to extract and analyze data from corporate databases.

I am stuck on the number formatting of the labels. My file is too big to attach; I have included the code below.

I'm sure there is something very basic that I am missing. Needless to say VBA is a challenge for me.

Option Explicit

Sub Atest()

Dim intR As Integer, intP As Long
Dim sngVal As Single, sngY As Single
Dim dtX As Date
Dim chtTst As ChartObject

Set chtTst = ThisWorkbook.Worksheets("GRAPHS").ChartObjects("Chart 18")
chtTst.Select
ActiveChart.SeriesCollection("Y Axis Values").DataLabels.Delete

View 5 Replies View Related

Format Chart Labels For Numbers With Commas

Mar 19, 2008

I know from searching through the internet you can change the intervals by modifying the minimumScale, maximumScale and MajorUnit fields of TickLabels. What I want to do is change the period of the axis label to a comma. For example,

if it shows

10.00
5.00
0.00

I want it to show

10,00
5,00
0,00

View 7 Replies View Related

Using Labels In VBA Code

Nov 19, 2008

In the following VBA Code. I have a section labeled tier2 and a section labeled tier3, as you can see.

Both of the calculation formulas are refering to the items listed.

Will the code in the Tier3 section ever refer to the values in the tier 2 section, since they have the same label names?

View 2 Replies View Related

Change Chart / Pivot Table Numeric Labels To Associated Text Labels?

Feb 7, 2014

I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.

For example, variable ASSIGNMENT has the following possibilities:

1
2
3
4

Here's what each of those "mean" (I have this in another table):

1 - Sick
2 - Overtime
3 - Court
4 - Present

How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?

View 8 Replies View Related

Userform Labels :: Change The Color Property Of All The Labels On The Form Simultaneously

Mar 11, 2009

I have a UserForm and what I'm trying too do is change the color property of all the labels on the form simultaneously.

View 9 Replies View Related

Userform, Labels Equals Range In Workbok, Easier Code

May 24, 2006

I have over 500 labels in a userform and each labels content is gathered from an workbok like this:

Private Sub UserForm_Initialize()
CommandButton3.SetFocus
Application. ScreenUpdating = False
Workbooks.Open "V:allaBeredningSemesterstänging Medleverantörer och Verkstäder.xls"
Sheets("2006").Select
' Sheets(Year(TODAY())).Select
LabelA1 = Range("A1")...............

I have shortened the code and I haven't yet written all labes. The ... is supposed to mean "from" and "to". I don't want to write over 500 lines of the label codes.
So what is an easier way to write it?

View 7 Replies View Related

Pivot Table - Show Row Labels Horizontally (Not As Column Labels)

Jan 16, 2014

I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.

View 1 Replies View Related

VBA Format Conditions For Next Loop

Nov 19, 2012

Problem: Placing a With/End With for FormatConditions in a For/Next loop isn't applying the conditional formatting.

Information:
- I have a stats section that has months as the column headers with totals at the end (used as a VLOOKUP table)
- Each column needs the conditional formatting separately
- The columns are static, but the rows are variable
- Taking it out of the loop and coding each loop individually works perfectly

The problem code:

finalRowTable = Cells(Rows.Count, 12).End(xlUp).Row
For i = 13 To 38
With Range(i & "11:" & i & finalRowTable)
.FormatConditions.Delete
.FormatConditions.AddTop10
.FormatConditions(1).TopBottom = xlTop10Top
.FormatConditions(1).Rank = 1
.FormatConditions(1).Percent = False
.FormatConditions(1).Interior.Color = 5296274
End With
Next i

I checked after running it and it doesn't show up in the Manage Rules Conditional Formatting

Code that works without loop:
Code:
With Range("N11:N" & finalRowTable)
.FormatConditions.Delete
.FormatConditions.AddTop10
.FormatConditions(1).TopBottom = xlTop10Top
.FormatConditions(1).Rank = 1
.FormatConditions(1).Percent = False
.FormatConditions(1).Interior.Color = 5296274
End With

I've been playing with excel VBA for a few months now, so it's probably something simple.

View 4 Replies View Related

Correct Format Of Do Until Loop

Jul 9, 2009

I have a question regarding Do While loops. I'm trying to write a do while loop to search through an array for a particular value and return the row number. This value is in the first column of the array and there are 211 of each value chronologically sorted. So, the first column from top to bottom reads 1,1,1,1..211 times, then 2,2,22,..211 times and so on. For this case, I want to return the first row where a particular value is found.

The loop I wrote is as follows: ....

View 9 Replies View Related

Conditional Format (vba Loop Is The Answer)

Oct 21, 2009

i have 3 columns and hundreds of rows.... i first applied conditional format to the first row (A1, B1, C1) only. no problems so far as A1 has value '0' which is minimum and B1 is the highest value '200' and C1 is variable value between 200' and 0'. Im using icon sets in this one.... now i want the below rows to have the same conditional format.... how do i do that because when i select them all rows and columns and try to apply conditioanl formatting the outcome is not the same as i got previously in the first row...it somehow combines all the values rather working only on the individual row. i hope u get wat im tryin to say....

i come across a solution in my mind to give a loop in vba to do the conditional formatting each row seperately untill empty cell of column no. 4.... but doing this will obviously eat lot of memory in excel cause of each row will have a seperate conditional format.....

View 9 Replies View Related

Loop Converting Date To TEXT Format

Jul 21, 2009

i'm trying to convert a column (P) from date dd-mmm-yy, subtract 5 days, and paste value as YYMM using a macro. I've borrowed bits from other macros and producted this but it's not working (and i've played around with it to the point it doesn't make sense anymore!)

Option Explicit

Sub ADD_REPORT_DATE()
Dim LR As Long, i As Long
Dim tempValue1
Dim tempValue2 As Date

Worksheets("Report").Select
LR = Range("P" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1

Set ActiveCell.Value = Text(ActiveCell.Value - 5, "YYMM")

Next i
End Sub

View 9 Replies View Related

Vba Code To Format Date In The Format Dd-mm-yy

Jun 13, 2006

I would like to format my cells containg date from any format to the format dd-mm-yy. I was doing this manually with the below function

=TEXT(YEAR(Q2),0)&"-"&(RIGHT("0"&TEXT(MONTH(Q2),0),2))&"-"&(RIGHT("0"&TEXT(DAY(Q2),0),2)).

I would like to automate this using vba. i tried a bit but doesn't get what i want. ultimately i want the date in text(dd-mm-yy) format.

View 5 Replies View Related

Loop Through Worksheets And Format Chart 1 - Runtime Error

Jun 27, 2014

I wrote a chart formatting macro, which works when applied to a specific sheet, and I'm trying use it in a for each loop. The macro is supposed to loop through a list of sheets and only format chart 1. I'm getting a "run time error 438: object doesn't support this property or method" at this line:

Code:
.ActiveChart.Shapes("TextBox 1").TextFrame.Characters.Text = "Bodily Injury (BI) Liability Claim Trends" & vbLf & " 2005-" & Range("K5").Value & " Percentage Change"

Here is my for each loop:

Code:
Sub UpdateTextAllStateCharts()
'This macro loops through worksheets in a list and performs the assigned task
Dim sheet_name As Range
For Each sheet_name In Sheets("WS").Range("C:C")
If sheet_name.Value = "" Then

[Code] .....

This line is also causing the same error:

Code:
With .Characters(42, 68).Font

View 2 Replies View Related

Looking For A More Memory-efficient Way

Mar 31, 2009

I am currently using Excel 2003 to pull from 2 external spreadsheets to compile data. The first sheet (seniority list.xls) being referenced contains all of our employees (approximately 350 rows, but that can vary from 340 to 450) as well as some information (employee ID, full time or part time, etc.). The second sheet being referenced (pay.xls) contains a breakdown of all employees and their pay for a particular bi-weekly period.

What I'm looking to do is:

(1) Find out all of my current employees. I do this by referencing seniority list.xls.

(2) For each employee, find out how many hours they worked each week. I need to do this by referencing pay.xls. Unfortunately, that file works quite differently than seniority list.xls. Unlike the latter, which contains one employee number per row, pay.xls could contain as many or as few rows per employee as their schedule dictates. Each row contains things such as overtime, regular time, double time, alternate regular time, alternate OT, vacation, sick pay, etc. In total, pay.xls could contain as many as approximately 8000 rows. To determine how many hours they work, I'm using this CSE (array) formula:

(Note that the employee ID is in column A of the current sheet.)

View 9 Replies View Related

Looking For More Efficient Way To Activate Arrays?

Sep 17, 2012

I have a bunch of array formulas that need to be activated by going on the cell, hitting F2, then hitting Ctrl+Shift+Enter. In the attached sheet I have done a record macro to automate this. I have another sheet with something similar just a lot more items on Sheet1 (~250 items). It will be very time consuming for me to record macro and hit F2, then hit Ctrl+Shift+Enter ~250 times so I am wondering if there is any way to activate all the array formulas on the sheet in one go using 1 formula. I'm not too concerned with file size.Also, not all arrays only include columns A and C, other columns (D, G, H and J) are included too.

View 3 Replies View Related

Cut & Paste Script - A More Efficient Way

May 13, 2006

I'm using the script below to do a cut & paste job. The script works fine. However, since I have a lot of data on my worksheet, it takes a very, very long time to complete the job. Based on the same logic used in my script, could you propose a more efficient way of doing it?

Sub test()
Application. ScreenUpdating = False
On Error Goto errortrap
Do
Columns("d:d").Select
Selection. Find(What:="*", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell. Offset(0, 1).Activate
ActiveCell.Offset(0, -1).Activate
Selection.Cut
ActiveCell.Offset(5, -2).Activate
ActiveSheet.Paste
Loop
errortrap:
End
End Sub

View 2 Replies View Related

Most Efficient Coding Method

Apr 25, 2007

In each VBA book I've read the authors will write their code like this:

nStart = UBound(GenArray1)
nEnd = UBound(GenArray2)
Redim preserve NewArray(nStart To nEnd)

Instead of the less line method of:

Redim preserve NewArray(UBound(GenArray1) To UBound(GenArray2))

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved