Understanding Code: Extend The Analysis

May 2, 2006

i need to extend the analysis but i have no idea what the 2 modules below do.

Sub mmm()
zonecode = Worksheets("sheet1"). Range("a65536").End(xlUp).Row
etypes = Worksheets("sheet1").Range("iv1").End(xlToLeft).Column
nextline = 2
For i = 2 To zonecodes
zcode = Worksheets("sheet1").Cells(i, 1).Value
For j = 2 To etypes
etype = Worksheets("sheet1").Cells(1, j).Value
enbr = Worksheets("sheet1").Cells(i, j).Value
Worksheets("sheet2").Cells(nextline, 1).Value = zcode
Worksheets("sheet2").Cells(nextline, 2).Value = etype
Worksheets("sheet2").Cells(nextline, 3).Value = enbr
nextline = nextline + 1
Next j
Next i
End Sub....................

View 2 Replies


ADVERTISEMENT

Understanding Code (code In Eglish Specially After The .Count /14-1)

Feb 6, 2009

Can someone explian to me this line of code in eglish specially after the .Count /14-1

View 2 Replies View Related

Understanding Automated Email Code

Dec 19, 2006

it has been designed to send out automated emails using Excel and Lotus Notes, unfortunately the code was give to me to modify and the person who created it didn't comment the code at all so i'm finding it impossible to see what each part is doing: ....

View 14 Replies View Related

Excel 2010 :: VBA Code To Extend Formula To (End Of Data)

Mar 4, 2014

I created the following macro (using the recorder), and now need to modify it so that the formula extends to the end of the data (and no further). I would also like to include a total at the end.

Sub ExcessUsage2()
' ExcessUsage2 Macro
Rows("39:250").Select
With Selection.Font
.Size = 8
.Strikethrough = False

[Code0 ....

View 8 Replies View Related

Inventory Analysis By Prod. Code

Apr 3, 2007

Hi there, I would really appreciate the help with being able to enter a code and having Excel find a match. I have attached a simple example of what I aiming for.

View 7 Replies View Related

How To Use Solver Add-in Then To Create Own Data Analysis Code

Nov 18, 2011

Is it easier to use the Solver Add-in than to create your own data analysis code? We are planning to design a scheduler for around 12 technicians in our contact center (doing phone support).

View 5 Replies View Related

Not Understanding VLOOKUP

Jun 30, 2008

I have searched for a while on VLOOKUP (something I'm not very familiar with but and trying to become more so), And if this wasnt such a time sensitive project I would continue to search. In one sheet, there is a column which includes employee #'s, the next column over is the employee names, next is salary. On another sheet I put a drop down box where you can select the employee number. In the cell next to that I would like to have the employees name come up as well as the salary in the next cell over....something like this

Name Salary
Dropdown w/#'s

Taken from


Emp# EmpName EmpSalary
23123 Joe Smith $50,000

Is this the right time for vlookup...there are almost 1000 names in this sheet?...

View 9 Replies View Related

Understanding Template

Dec 8, 2008

I have a workbook with several worksheets and numerous userforms, commandbuttons and macro's. I want to prevent people from saving over the workbook once they have used it, but allow save as using a different workbook name.

I tried saving the book as .xlt, but then when I reopen it and make material changes, it lets me save over it. I have't worked with templated before, is there something I am doing wrong?

View 9 Replies View Related

Data Analysis: Place Called Data Analysis Under Tools

Nov 22, 2006

in excel, is there a place called data analysis under tools? im an exchange student in sweden andthe work that teacher gave me is half swedish and half english.. im looking for histogram there.

View 3 Replies View Related

Sumproduct Understanding Cell Value

Mar 26, 2014

I have this formula -

=SUMPRODUCT(('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$E:$E=B4)*('[Australia Load Tracker - March 2014 - V2.xlsm]QA'!$D:$D=$A$2))

Which works perfectly if A2 has the cell value 24/03/2014 however, if i put in $A$2 this value

="02"&"/"&$XFC$1&"/"&"14", where XFC$1 equals =IF(XFD1>9,XFD1,"0"&XFD1) and XFD1 = =MONTH(TODAY())

Which in basic term gives the month value "03"

The cell shows the value 24/03/2014 however does not work with the sumproduct.. is there anyway to get sumproduct to read this cell value as 24/03/2014 as the whole point is so every month I don't have to update anything and it will constantly update...

The only alternative i can think of is quickly making a VBA script to hardcode the cell values for the dates and change the month value for the middle of the cells every month.

View 3 Replies View Related

Understanding How FindFirst Works

Mar 5, 2008

How do I use Find to locate the first occurence of a particular value in a cell, and return the row that the cell is in?

View 14 Replies View Related

Public And Private Sub: Move To The Next Curios Level Of Understanding Macro And VB Coding

Jun 29, 2006

Where can I obtain some working examples or explanations as to what, where, when, why, and how these Public and Private Sub are correctly implemented? I see these code used extensively but not sure why it's there yet. A macro which uses a Private Sub is in one of my workbooks. Due the "progress" of things from here I now need to move to the next curios level of understanding Macro and VB coding.

View 3 Replies View Related

Extend Column Instead Of Row?

Dec 16, 2013

When i press the corner of a cell and drag it (example =D2) it will only go higher in the number (drag it 3 rows down it just goes D2, D3, D4, D5, D6).

Can I drag it down but make it stick to the 2 and instead go E2, F2, G2 etc?

View 14 Replies View Related

Extend Highlight Bar

Sep 22, 2009

I am editing a fairly sizeable name, address list and I would like the row highlight bar to extend across the page. For instance, when I click on the row number the highlight bar extends across the row, but when I hit return the highlight disappears and I have to click row by row. I would like the highlight bar to extend across a row and then move up or down with the arrow keys.

View 5 Replies View Related

Extend A Constant Array

Jul 30, 2008

I've never had to venture into Arrays as there was no need.

I'm now faced with Arrays.

Generally I'm able to figure things out, in this case I'm stumped.

What I have is a Constant Array, that output the amount of Rows the Array displayed in.

I know the information is there, what I don't know is how to extend the rows.

ie it Outputs 5 Rows right now, with a top row header.

What I want is 10 rows with a header.

View 9 Replies View Related

Automatically Extend Formulas

Sep 6, 2007

Right now I have a spreadsheet where I have a person enter in 4 differant values in 4 adjacent cells of a row, then in another 4 adjacent cells of the same row calculations are done with respect to the 4 rows of entered information then displayed. Is there a more effective way in placing and copying these formulas in the last 4 adjacent cells rather then just copying the formula into the first 3000 or so rows of cells?

I say 3000 because each project is differant and will require a differant amount of rows, but by copying the formula, even little IF statements, slows down the program and makes printing a hassle.

View 9 Replies View Related

Extend Length Of Dropdown List

May 12, 2009

Is there a way to make the dropdown list come up longer than standard?

View 4 Replies View Related

Extend Worksheet To 1 Million Rows?

May 20, 2009

I have a worksheet that was originally saved as a xls (2003) file. I have resaved it as a xlsx (2007) file, but the max rows are still at 65,536. I am trying to combine over 100,000 records into one sheet. The only way I have been able to create a excel document with more than 65,536 rows is to export my records from an Access file.

View 2 Replies View Related

Extend The The Number Of Values In A List Box

Nov 3, 2008

I am using Excel 2003 SP3. How does one extend the the number of values in a list box and retain the same name to be used in the Data / Validation / Settings / Source entry? The only method I see is to extend the list of allowable values and give the longer list a new Name.

View 2 Replies View Related

Formula That Will Automaticaly Extend A Series

Feb 23, 2010

I was trying to set yp a formula that will automaticaly extend a series. I mean will add a sequential number to the one above. If I have in cell A1: CD001. I want to place a formula that will show me: CD002 in cell A2. I tried n function but it only converts it to the number and comes uout as 0. I don't know which one to use if there is any. Answer to the first question I will probably be asked: No I cannot drag it down with a mouse as I will place it in the conditional function afterall.

View 5 Replies View Related

Extend Selection Until Cells Are Empty

Jun 15, 2014

If my cursor is in A1 and I want to select all non-empty cells in that column, what is the VBA code that will do that?

(I'm a beginner and have tried recording a macro and inspecting the code, but that always gives me a specific number of cells to select, while I want it to vary by the number of cells that have values. )

View 8 Replies View Related

Extend Non-contiguous Row Selection In Macro

Sep 16, 2008

I get 40~100 page PDFs of purchase orders every week. Each page has one part number and its open order and forecast information. Some pages have a few line of information, some have many lines.

I convert this file into a txt file.

I then create a macro to import it as space delimited and format it.

What I need to do it get rid of the header/footer information on EVERY page.

When the txt file is created it doesn't have any page marks.

The header has "ABC Widgets" as the customer name and then 10 rows of unnecessary information. If I do a Find ALL for "ABC Widgets" and select all, it will delete that header line at every instance, but the leftover selected cells then start not-lining up (meaning I can't just hit "delete row" 10 times and have it pick the right row--the rows don't line up any more and each instance is -1 row offset.)

Is there any way to extend the selection of non-contiguous rows (starting with ABC Widgets) down by 10 so I can delete them?

View 9 Replies View Related

Extend Formula When Rows Added

Jan 5, 2007

I have a sheet (Sheet 3) that is pre-populated from another sheet (Sheet 1), and the user is allowed to update the numbers (for forecasting).

I have a column ( Total Hours) that I use to total the new numbers in the row.

If the user inserts a row, the Total Hours formula does not follow. This is the Total Hours formula that I am using SUM Formula
(=IF(SUM($G30:$AP30)=0,"",SUM($G30:$AP30))

How do I (Can I) get the formula to cascade into the new row.

View 8 Replies View Related

Extend Range To Longest Column

Aug 7, 2007

I had asked about automatically naming regions and this is an extension of that post because it's closed. The code we ended up with to name the region is:
Dim sNm As String, sRT As String

If Intersect(Target, Rows(1)) Is Nothing Then Exit Sub '------------------->
If Target.Count > 1 Then Exit Sub '---------------------------------------->

sNm = Replace(Trim(Target), " ", "_")
sRT = "=offset(" _
& Target.Address _
& ", 1, 0, counta(" _
& Cells(2, Target.Column).Resize(Rows.Count - Target.Row).Address & ") )"

ThisWorkbook.Names.Add Name:=sNm, RefersTo:=sRT

My problem now is that I need to have the regions be the length of the longest column. I've tried using a few different ways using the worksheet range but I can't seem to get it to work.

View 9 Replies View Related

Extend Range Of Table Automatically To Fit Data

Jul 14, 2014

I have two data tables (Table1 and Table2) on two different sheets (Sheet 1 and Sheet 2). In Col1 of Table2, I have "copied" the values of Col1 in Table1 by using a simple equal (=) formula. Secondly, I have an autofilter on Table2 and a macro that automatically updates the filter when the sheet (Sheet 2) is selected.

My problem is that I would like Table2 to be more dyanmic, i.e. I don't want to change the range of the Table2 each time I add, or subtract, a value from Table1.

Could a OFFSET formula be employed in any way?

I have attached an example file.

The macro is:

[Code] .....

ExpandTableExample.xlsm‎

View 1 Replies View Related

Isnumeric Function - Getting Range To Extend By 1 If Not Numeric

Apr 29, 2014

So I found a great piece of code which calculates relative strength index. However, I've been trying to edit it to make it a bit more dynamic and I'm really struggling to get it to work.

Right now the initial code is:

Code:
Function RSI(MyCells As Range)
Dim up_day, down_day
Dim average_up, average_down
Dim RS, cellcount As Long
Dim cll As Rangeups = 0up_day = 0downs = 0down_day = 0cellcount = 0For Each cll In MyCells

[Code] ..........

Basically I tried to amend this as follows:

Code:
Function RSE(MyCells As Range, Length As Double)Dim up_day, down_day, ups, downs
Dim average_up, average_down
Dim rs, cellcount, rangecount As Long
Dim cll As Range
ups = 0

[Code] ..........

Basically i wanted it to look at the numbers in the range and then error check to see if it was a number... if it was a number great. If not then the length of numbers it should calculate across should increase by 1 as it has to include another number to calc across (hope this makes sense). however, i just cant get it to work at all. My code doesn't seem to make any difference. And it also raises the issue of the fact that the intial value for length also needs to be stored to do the final calc.

View 1 Replies View Related

Extend Formula Automatically As Data Entered

May 21, 2008

I found the following code here.

For data entered into column A, it copies the formulas from columns B:E in the row above to the current row.

It works great except fot the first row (A9) where it copies the header row (B8:E8).

How can I get it to not copy when data is entered into A9?

Private Sub Worksheet_Change(ByVal Target As Excel. Range)
Dim c As Range, i As Long
On Error Resume Next
Set c = Intersect(Target, Columns(1))
If c Is Nothing Then Exit Sub
If IsEmpty(c.Offset(-1, 0)) Or Not IsEmpty(c.Offset(1, 0)) Then Exit Sub
i = c.Row
Application.EnableEvents = False
Range("B" & i - 1 & ":E" & i - 1).Copy Range("B" & i & ":E" & i)
Application.EnableEvents = True
On Error Goto 0
End Sub

View 4 Replies View Related

Automatically Extend Pie Chart To Include Categories When They Have Values

May 29, 2014

I have a pie chart on a pre-defined table with 15 categories where the data will populate over time (presently only 3 are populated) in descending order. As some categories will be blank/zero for a while, I'm trying to find a way to avoid showing the blanks/zeros on the pie chart. As I am trying to display the legend on the pie chart segment, any zeros cause the remaining labels to appear grouped together and rather messy. I would therefore like to hide the zero data from the pie chart so the labels do not appear, or to automatically extend the range to only include all non-zero values.

I found the following formula which, when used as a named range, automatically extends a list for data validation:

[Code] ....

I don't know whether there's a similar formula or whether a few lines of VBA are required to run whenever the chart or data worksheet are selected..?

View 2 Replies View Related

Extend Formula Each Time Data Entered In Column Of Next Row

Nov 30, 2012

I have a spreadsheet that requires a formula in column "e". How can I automaticlly extend the formula each time data is entered in column "d" of the next row.

View 7 Replies View Related

Extend Formula As Many Data Rows As Referenced Column

Oct 31, 2006

I am after an automatic formula or function which calculates values for long lists instead of me dragging down the formula all the way to the end of the document.

Hence, I have a long list of data in columns A, B&C and I want the formula in column D to automatically be calculated all the way down when the list stops.

View 9 Replies View Related







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