Free Microsoft Excel 2013 Quick Reference

Insert row with functions Results

Last time I posted, I used such a weak example, that when JLatham
responded with his answer below... all it showed is that it's
possible, but because my example was so week the answer didn't help
much... after the quote I want to pose a real life example of the
question I'm trying to ask with real figures...

> As long as your formula can always be expressed as you've shown it we
> can use
> VLOOKUP() and a table for the 12 amounts to calculate it.
> You sholwed 2 formulas:
> (12/6)-(1/3)
> and
> (11/6)-(1+(2/3))
> but the first one could also be expressed as
> (12/6)-(0+(1/3))
> So a general expression would be:
> (X/a)-(b+(c/d))
> where 'b' can be zero or some other number. I used 'a' instead of 6
> because
> perhaps it isn't always 6?
>
> Lets say your amounts are in column A and you want the commission to
> show up
> in column B next to it. In A1 you put 11 and in A2 you put 12, and
> continue
> down column A entering one of those 12 possible amounts.
>
> Now set up a table somewhere - for this example I'll start it at F1 and
> it
> will take up 12 rows and 5 columns:
> F G H I J
> 11 6 1 2 3
> 12 6 0 1 3
> 25 6 2 1 3
> 30 6 0 1 3
> 35 6 1 2 3
> 40 6 2 3 5
> 45 6 0 3 5
> 50 6 1 2 5
> 55 6 2 2 5
> 60 6 0 1 3
> 65 6 1 2 3
> 70 6 2 1 4
> amt 'a' 'b' 'c' 'd'
>
> In B1 you would enter this formula:
> =(A2/VLOOKUP(A2,$F$1:$J$12,2))-(VLOOKUP(A2,$F$1:$J$12,3)+(VLOOKUP(A2,$F$1:$J$12,4
> )/VLOOKUP(A2,$F$1:$J$12,5)))
>
> that should all be on one line - the format here may break it into 2 or
> more
> lines.
> Then just drag that formula down the page. Substitute the actual
> location
> of your table for $F$1:$J$12 in the formula.
>
> By the way - the reason your previous effort failed probably wasn't due
> to
> the line being too long, but because you had to try to use more than 7
> nested
> IF() statements - Excel has a limit of 7 nested functions in a
> formula.
>
> One more thing - if you don't have an "amount" in column A, then you'll
> get
> a #NA error in column B where you've placed the formula - this
> variation of
> the formula will fix that, again, just one long line:
> =IF(ISNA((A2/VLOOKUP(A2,$F$1:$J$12,2))),"",(A2/VLOOKUP(A2,$F$1:$J$12,2))-(VLOOKUP(A2,$F$1:$J$12,3)+(VLOOKUP(A2,$F$1:$J$12,4
> )/VLOOKUP(A2,$F$1:$J$12,5))))

Ok....

There are 12 variables that a person could enter in the box... however
rather than waste your time I'm just going to give 2... I should be
able to extrapolate how to do the rest...

115, and 225

now it's 115 for the year, and 225 for the year...

however, 115 matches up to 9.89/month and 225 matches up to 19.35/mo

So, if you figured it out you'd quickly find out that 9.89 does not add
up to 115 over the course of a year... in fact, it's more. and same
with the 19.35

So I first need to force 115 and 225 to match their respective monthly
charges...

Then I need to say for the first 6 months you make 200% ... the second
6 months you make 10%

and here's my code (and it does work) ... A1 in this example is the
quantity
purchased

example for 115:

IF(A1=0,0,(A1*(((((115/12)+((23*A1)/(75*A1)))*6)*2)+(((115/12)+((23*A1)/(75*A1)))*6)*0.1)))

example for 225:

IF(A1=0,0,(A1*(((((225/12)+((3*A1)/(5*A1)))*6)*2)+((((225/12)+((3*A1)/(5*A1)))*6)*0.1))))

Now... the problem I face is that they can insert up to 12 different
numbers.... but of course I only told you 115 and 225 in this
example....

but.... if the enter 115 ..... I need Excel to figure out the first
code

but if they enter 225 ... I need Excel to figure out the second code.

I tried to make this question as clear as possible... hopefully you'll
understand what I'm trying to ask.

--
rjmckay
------------------------------------------------------------------------
rjmckay's Profile: http://www.excelforum.com/member.php...o&userid=16880
View this thread: http://www.excelforum.com/showthread...hreadid=550681

I am finding Excel 2003 a absolute nightmare to use. IMO it has been changed
to the point of unusability in some respects.
However, I must move with the times. I have found some workarounds thanks to
the suggestions from this forum and I do like many of the features in 2003
but my current problem is driving me absolutely crazy.

I very frequently reference other cells, often cells which are in a Pivot
Table. To do this is Excell 97 or 2000 I would click the "=", then click the
cell i want to reference. The "=" has disappeared, replaced with the almost,
but not quite totally, useless, "fx" button. I now use a custom "=" insert
button, which works fine for me.
Anyway, Pivot tables have always been a problem to chart, you can't use the
data in a scatter plot, so I normally just reference the data in the PT in
the adjacant cells.
What Excel 2003 has done to me, (obviously a personal insult directed at me
specifically) has automatically replaced the A1 cell reference with a load of
unnecessary garbage, WHICH IS AN ABSOLUTE REFERENCE, completely negating the
shortcuts i am accustomed to using when dealing with large amounts of data.

For example, instead of click "=", Click cell (in a pivot table) G12,
producing "=G1"
it produces this:
=GETPIVOTDATA("gm
x",$A$10,"xory","x","focus",9573,"zone",0,"col",1, "row",1,"xnom",6000,"ynom",6000)

An absolute reference, a load of unnecessary crap and basically unusable
when I want to build a formula from this as a starting point. Not only is it
an absolute reference (the $A$10 bit) but it is referencing the cell I
clicked as another, within function, undragable, locked to the text function
argument, cell of the pivot table.

I have to click "=", click and drag a vector , eg =G12:G14 and manually
remove the ":G14" part, in order to make it function "normally".
This has me on the verge of tears on the late nights when I am trying to
deal with the massive amounts of data I am trying to analyse before the
meeting at 9 the next morning...

The question: Can I turn off this automatic "GETPIVOTDATA" function
insertion when I simply want to reference a cell in a pivot table?

Thanks for your patience

Incoherent

Thanks, Access Expert! Your formula works really great!

Now, I am trying to use the same formula to remove the last three characters
which contain a comma, a space, and one letter (it varies, and a wildcard
doesn't seem to work) (e.g. ", J"), how do I modify it to remove the last
three characters. I've been playing around with it, but don't know how to
specify the letter as a wildcard, if that's even possible.

I want to to go from Doe, John, W --> Doe, John

Thanks!

"AccessExpert" wrote:

> Mira,
>
> You can use a combination of the Left and Right functions. Insert a blank
> column to the right of column you wish to change. In my example I will
> assume the data is in column A. Change the formula as necessary.
>
> In the empty cell enter the formula:
> =IF(RIGHT(A1,1)=",",LEFT(A1,LEN(A1)-1),A1)
>
> This formula is checking to see if the last character in cell A1 is infact a
> comma. If it is the formula will return all of the characters in cell A1
> except the last one. If it is not a comma then will just return the entire
> contents of cell A1.
>
> Copy this formula down to all the rows necessary. Then Copy the entire
> column and paste the values over the original column (A). Make sure you use
> the Paste Special and choose the Values Option. Now you should be able to
> delete Column B.
>
>
> "mira" wrote:
>
> > I am trying to remove a comma which happens to be the last character in the
> > cell. Does anyone know how to do this?

Pagal

It sounds like you need something like this

Dim i as Long
Dim ColNo as Long

For i = 1 to Me.Controls.Count
ColNo = Application.Match(Me.Controls(i).Tag,Pigs.Range("R ow with
fieldnames"),False)
Pigs.Cells(LastRow of datatable, ColNo).Value = Me.Controls.Text
Next i

The Match function determines which column contains the field name, then
rights the text property of that control to that column.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Pagal" > wrote in message
om...
> Hi,
>
> I am building a user form in Excel. This is brought up on clicking a
> button on a form.
>
> In the form, I display data from a data sheet (data table):
>
> Field Name -> Name Age Address
> Data -> ABCD 12 123, ....
> Data -> EFGH 34 456, ....
> Data -> IJKL 56 789, ....
> Data -> MNOP 78 012, ....
>
> using HLOOKUP as
> oTextBox.Text = Application.WorksheetFunction.HLookup(oTextBox.Tag ,
> Pigs.Range(Pigs.Cells(1, 1), Pigs.Cells(m_iRecordCount + 1,
> m_iFieldCount)), m_iCurrentRecord + 1, False)
>
> oTextBox.Tag = Field Name, inorder to avoid writing field-specific
> function to populate the UI.
>
> works fine.
>
>
> Now, I need to insert data into the data sheet (at the end) using the
> field name.
>
> What I want to do is - When the users clicks on an 'Insert' button on
> the form, I want to go to the last row of the table. Iterate through
> the textboxes and using their 'Tag' property to identify the column,
> insert its text.
>
> I haven't been able to figure this out yet.
>
> Any pointers on how to do this would be very welcome.
>
> Thanks,
> Rajesh

I have two UDFs- the first one returns the expected value (so I assumed it
was working), but then I modified it to create a second UDF (which doesn't
work) and I think they both have the same problem- I'm passing single cell
ranges as arguments to my UDFs, but when I try to set a temporary range
(ws) equal to the range that was passed as an argument, it sets the new
range (ws) to the value of the argument's range, rather than the range
itself. The two UDFs are listed below, with comments inserted to clarify
my problem. If anyone can tell me how to set the ws, ws1, and ws2 ranges in
code to the arguments of the UDF, I'd really appreciate it!
Many thanks,
Keith R
XL97

This one returns the desired value, although is still sets ws to the value
of the cells, not the range (afaik)
=============================================
Public Function ConsolidateMe(SC1 As Range, SC2 As Range, SC3 As Range, _
SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _
SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _
SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _
SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range) As Variant
'each input range will be a single cell

Dim i As Integer
Dim ws As Range
Dim TotalValue As Double
Dim DivCount As Double

For i = 1 To 19
Set ws = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9,
SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19))
'Once I debugged, I realized that this is still passing the
'_value_ of SC1, SC2, etc (I think) because when I pause
'code execution and mouseover, ws shows the value in
'the SC range that is currently being looped

If Not IsError(ws.Value) Then
If Not IsEmpty(ws.Value) Then
If IsNumeric(ws.Value) Then
TotalValue = TotalValue + ws.Value
DivCount = DivCount + 1
End If
End If
End If
Next
If DivCount > 0 Then
ConsolidateMe = TotalValue '/ DivCount
Else
ConsolidateMe = CVErr(xlErrNA)
End If

End Function

=============================================
Here is the second function, which fails at the offset
command, presumably because it is trying to offset
from a value instead of from the range?
=============================================

Public Function WeightedConsolidateMe(SC1 As Range, SC2 As Range, SC3 As
Range, _
SC4 As Range, SC5 As Range, SC6 As Range, SC7 As Range, _
SC8 As Range, SC9 As Range, SC10 As Range, SC11 As Range, _
SC12 As Range, SC13 As Range, SC14 As Range, SC15 As Range, _
SC16 As Range, SC17 As Range, SC18 As Range, SC19 As Range, _
NumRowShift As Integer)

Dim ws1 As Range
Dim ws2 As Range
Dim NumVal As Variant
Dim DenomVal As Variant
Dim TotalNum As Variant
Dim TotalDenom As Variant
Dim DivCount As Variant

For i = 1 To 19
Set ws1 = (Choose(i, SC1, SC2, SC3, SC4, SC5, SC6, SC7, SC8, SC9,
SC10, SC11, SC12, SC13, SC14, SC15, SC16, SC17, SC18, SC19))
If Not IsError(ws1.Value) Then
If Not IsEmpty(ws1.Value) Then
If IsNumeric(ws1.Value) Then
NumVal = ws1.Value
Set ws2 = Application.WorksheetFunction.Offset(ws1, 0,
NumRowShift)
'it just stops here- no error or anything- it just stops....

TempRow = Application.WorksheetFunction.Row(ws2)
tempcheck = TempRow Mod 50

Do Until tempcheck = 5
If Not IsError(ws2.Value) Then
If Not IsEmpty(ws2.Value) Then
If IsNumeric(ws2.Value) Then
DenomVal = ws2.Value
tempcheck = 5
Exit Do
End If
End If
End If

tempcheck = tempcheck - 1
Set ws2 = Application.WorksheetFunction.Offset(ws2, -1, 0)
Loop

TotalNum = TotalNum + NumVal
TotalDenom = TotalDenom + DenomVal
DivCount = DivCount + 1
End If
End If
End If
Next
If DivCount > 0 Then
WeightedConsolidateMe = TotalNum / TotalDenom
Else
WeightedConsolidateMe = CVErr(xlErrNA)
End If

End Function

Hi All,

I am using the function and the output is "#NA" - there's obviously
something wrong. I've checked the field are the same format etc.

To test the function I do a find on a value from the table array into the
lookup worksheet. It finds the value so the function should work.

Having verified the val exists the function automatically updates and
inserts the value from the table array. When I then copy & past the same
row the output is "#NA". Again, when I use the find feature it updates the
function. The calculation is as follows:

=VLOOKUP(C25,Data!$A$2:$B$317,2,FALSE)

Can anyone assist with identifying the cause?

Thanks in advance,
H

Dear Sir,

My posting entiltled "Matching Data in Excel 2002" on October 18, 2006 refers.

I would like to put forward a suggestion to add a Data Matching Button to
Excel tool bar as per my discussion on the forum.

I think MS Office 2007 (Beta) is at its finalising stage, I do not know you
still have time to include this if my suggestion is sound and wise.

Anyway please keep in view for my suggestion future versions.

I am using Seagate Infordesktop Software that generate reports in Excel 2002
format. I need to do the matchng of block of data collected from different
sources on the same spreadsheet quite frequently. This data file usually very
big. II am not able to program the spreadsheet as my programing knowledge is
very limited.

If my suggestion is implemented in your future version, it will certainly be
a great help to me and many users around the world.

Kind Regards

Low Seng Kuang
Maslaysia

PS: For your easy reference, the online discussion reads at MS Office
Discussion Group reads as follow:: < Please read from the bottom>
.................................................. .................................................. .....................

Dear Sir,

Thank you for your instant reply.

As I do not have any groundwork on programing, I find it very difficult to
understand these steps.

At present MS Office 2002 - 2007 still do not have a simple and easy to use
button at the tool bar for this data matching function. I would like to put
forward to a suggestion to Microsoft to develop this button in the coming
version.

I have the folowing ideas in my mind to share with the community:

This button will be something like the present "Sort Button", maybe we
could call it a "Matching Button". This button will allow the matching of the
lines in different blocks of data at the same worksheet with common matching
refrencece.

The matching function could be done for lines of data up to 4 to 5
highlighted blocks or more. Lne with the same matching refrence from
different block must appear in the same line after matching, with blank cells

The steps of the matching is first to click at the Matching Button, then
select the first block of data to be matched , then select its matching
refrence column say ( B2..B500).

After this, select the second block of the data to be matched with, then
select its matching reference say (K2.. K500), and finally select output
refrence in (B2..B500) to be in ascending or decending order.

The final output will have the line with identical matching refrence appear
at the same row of the worksheet without altering its original content.

I believe this Matching Button is not difficult to designed under today's
advanced technology. It certainly would help the users with very litlle
programing background to do the matching task at their work place.

Also this button could prevent errors ( say key in a wrong cell refrence
etc) when the task is done by programming the spreadsheet.

I hope my input and suggestion is helpful for the Microsoft as well as the
community.

Kind Regards

Low Seng Kuang
Malaysia

A36B58K641

================================================== ===

"bj" wrote:
There are
> assuming block a is columns A and B
> and block b is columns C and S
> and the data to be compapered starts in row 3
> I would first sort Block A
> Then sort block B
> and run a macro similar to
>
> sub sortwithblanks()
> r = 3
> 10 if cells(r,1) = 0 and cells(r,3) = 0 then goto 99
> if cells(r,1)=cells(r,3) then goto 20
> if cells(r,1)>cells(r,3) then range(cells(r,1),cells(r,2).select
> if cells(r,1) Selection.Insert Shift:=xlDown
> 20 r = r+1
> 99 end sub
There are, of course, many ways to do this.

================================================== ===

> "Mr. Low" wrote:
>
> > Dear Sir,
> >
> > I need to match each row of the data from block A with each row of data from
> > block B with the common reference.
> >
> > May I know how to do it.
> >
> > My illustration is as follows:
> >
> > Before Matching
> > Block A Block B
> > Ref Data Ref Data
> > R1 xxx R6 xxx
> > R6 xxx R1 xxx
> > R8 xxx R2 xxx
> > R9 xxx R3 xxx
> > R2 xxx R4 xxx
> > R3 xxx R7 xxx
> >
> > After Matching
> > Block A Block B
> > Ref Data Ref Data
> > R1 xxx R1 xxx
> > R2 xxx R2 xxx
> > R3 xxx R3 xxx
> > R4 xxx
> > R6 xxx R6 xxx
> > R8 xxx
> > R7 xxx
> > R9 xxx
> > < Ummatched data will have a empty column in its right or left as illustrated>
> >
> > What formulas do you use and what are the key board steps please ?
> >
> > Thanks
> >
> > Low
> >
> > --
> > A36B58K641

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I will try to explain clearly what I am trying to accomplish. I hope this
makes sense.

I work in the transportation Industry and have the dubious pleasure of
designing a spreadsheet, which may be outside of my skill level.

The Highway Traffic Act in Ontario, Canada stipulates that a truck or bus
driver is restricted to the following hours of work:

A driver may not drive a truck or a bus after being on duty for,
a) 60 hours in 7 consecutive days, or
b) 70 hours in 8 consecutive days, or
c) 120 hours in 14 consecutive days.

I have driver’s names and their assigned work hours in a separate table and
I can bring the regular hours into the worksheet using a lookup function.
What I need is a way to track and warn me if an operator exceeds the
allowable hours in any 7, 8, or 14 day period. The work periods are not
static. To put it another way, the drivers are always working the 7th, 8th or
14th day.

As an example;
A driver starts work on Monday, before he reaches Sunday he has accumulated
60 hours and must not continue to work during the remainder of this 7 day
stretch so he is forced to take Sunday off. He returns to work on Monday. His
seven-day stretch does not start over. He must now calculate the hours worked
from the previous Tuesday to determine how many hours he is allowed to work.

Below is a formula I received from a gifted Excel professional but it does
not quite work. It is my fault for not making my problem clear enough. This
formula has made me re-think my strategy and I am now asking for assistance
based on this new format. I think this formula could work with a few
modifications.

I have decided to take the sound advise of using one spreadsheet instead of
several with a few additions.

I would like to create a template and use the file for each driver who works
extra hours. In A1 I would type a drivers name, which would return values via
a lookup table to represent a scheduled set of hours over a 12 week period.
These hours would be imported via the lookup into B3:B??. Each workday would
be listed by date from A3:A??. Then the formula below would be used for the
calculation and the Conditional format command would warn me of any
violations. The regular scheduled hours for the driver would occupy the dates
for the entire 12 weeks. Days off would be blank. I would insert another
column or columns that the extra work could be enter in and column B3:B??
would contain a nested formula to return this value.
Column C3:C??, D3??, E3:E?? respectively would be used to hold the formula
below. I have tried to modify it but it will not work for me. Any help would
be appreciated.

Thank you in advance,
Martin

Perhaps you could store all the data on the same sheet. Then, keep a
running total per limit per driver.

For example, with dates in column A, names in column B, and hours in
column C, in cells D1:F1, enter the hour limits (60,70,120).
In cells D3:F3, enter the consecutive days (7,8,14)

In cell D4 enter:
=SUMPRODUCT(--($A$4:$A4>=$A4-D$3-1),--($B$4:$B4=$B4),--($C$4:$C4))

Copy this formula across to column F, and down to the last row of data.

Use conditional formatting to highlight the cells that are over the
limit in row 1.

A pivot table could summarize the data by week.

There's a sample file he

http://www.contextures.com/excelfiles.html

Under 'Conditional Formatting', look for 'Highlight Amounts Over Limit'

I have a pricelist/configurator helper workbook that I have produced in Excel with 12 worksheets (8 visible/4 hidden). My sales people enter nummeric data in columns matching the item they want in their proposal in each of the 8 visible sheets. Each sheet contains a different part of the product mix being proposed. The inputs are automatically copied in cells in the hidden sheets where the pricing function occurs. The inputs are also copied to the last visible worksheet that is the "pricelist" for our proposal.

Here's the issue.

After the input of each proposal is complete, the salesperson has to manually delete any rows that do not contain a zero quantity in the last worksheet (the "pricelist'). I have seen other Excel spreadsheets that automatically insert the rows that have numeric data in them in a logical fashion. I have never pursued how to do it but our proposals are now large enough to cause this to be a MAJOR inconvenience to the sales reps. I would like to have the last sheet in the workbook automatically populate with data from the other worksheets that have a numeric quantity associated with them.

Is there a plausible way to do this without being a Jedi Master of VBA?

--
Message posted via http://www.officekb.com

Hi all,
This is my first VBA Projet and whilst it works (in it's current stat
/ content), I've stumbled across some problems.
So if you are willing to amble through my ramblings and assist, I'll b
greatfully apreciative of any suggestion / solutions you are willing t
offer.

The Named Array's: 'Doctors' / 'Doctors Array' / 'Doctors Table' al
have data in them currently. And when using the 'RemoveButton' wil
successfully remove all records. But when I attempt to add a recor
fails due to the 'Doctors' Named Array being a #REF error due to th
last record being removed (so I'd concluded).

I attempted to add a dummy record and retried to add a record, but i
again fell over as (-and I'm assuming here again-) due to not enoug
records for the script to work.

My questions a a
- Do I need to re-write what I've completed thus far ?
- Need to build a user function to handle this problem?
- Have I overlooked some basic fundamentals in the design of th
script?
- Is my existing code well structured??
- Areas of improvement ??
- Need to see a shrink?aAgain any help is apreciated.
-If anyone would like a copy of the XLS to view, let me know.-

Cheers,
Cameron
-Brisbane, Australia-

------------------------------------------------
Code in Worksheet "*Extra Tables*"...

Code
-------------------
Private Sub AddButton_Click()
'ADD DOCTOR
DisableButtons
frmGetDetails.Show
ShowButtons
End Sub

Private Sub DeleteButton_Click()
'REMOVE DOCTOR !!
DisableButtons
frmDltDetails.Show
ShowButtons
End Sub

Public Sub DisableButtons()
CommandButton1.Enabled = False
CommandButton3.Enabled = False
ThisWorkbook.Worksheets("Extra Tables").Range("A1").Select
End Sub

Public Sub ShowButtons()
CommandButton1.Enabled = True
CommandButton3.Enabled = True
ThisWorkbook.Worksheets("Extra Tables").Range("A1").Select
End Su
-------------------
Code in Userform *frmGetDetails*...

Code
-------------------
Sub ClearText()
'Sub to clear values of all TextBox's
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
End Sub

Private Sub CancelButton_Click()
'Hide this form & unload
Me.Hide
Unload Me
End Sub

Private Sub ContinueButton_Click()

Select Case Len(TextBox1.Text)
Case 0
MsgBox "Information Required - Doctors Name." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
ContinueButton.Enabled = False
TextBox1.SetFocus
'Drop out of this Sub
Exit Sub
End Select

Select Case Len(TextBox2.Text)
Case 0
MsgBox "Information Required - Brief Detail." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
TextBox2.SetFocus
'Drop out of this Sub
Exit Sub
End Select

Select Case Len(TextBox3.Text)
Case 0
MsgBox "Information Required - Address Line 1 Details." & vbCrLf & _
"This field cannot remain empty.", vbInformation, "Missing Data!"
TextBox3.SetFocus
'Drop out of this Sub
Exit Sub
Case Else
Select Case Len(TextBox4.Text)
Case 0
Resp1 = MsgBox("Information Required - Address Line 2 Details." & vbCrLf & _
"Are you sure this line is to be empty?", vbYesNo, "Missing Data!")
If Resp1 = vbNo Then
TextBox4.SetFocus
'Drop out of this Sub
Exit Sub
End If
Case Else
Select Case Len(TextBox5.Text)
Case 0
Resp1 = MsgBox("Information Required - Address Line 3 Details." & _
vbCrLf & "Are you sure this line is to be empty?", vbYesNo, "Missing Data!")
If Resp1 = vbNo Then
TextBox5.SetFocus
'Drop out of this Sub
Exit Sub
End If
End Select
End Select
End Select

'Prompt User with a Message Dialog Box to confirm details.
Resp2 = MsgBox("Please confirn the following details:" & vbTab & vbCrLf & _
"Doctors Name:" & vbTab & TextBox1.Text & "." & vbTab & vbCrLf & _
"Brief Detail:" & vbTab & TextBox2.Text & "." & vbTab & vbCrLf & _
"Address Details:" & vbCrLf & _
vbTab & vbTab & TextBox3.Text & vbCrLf & _
vbTab & vbTab & TextBox4.Text & vbCrLf & _
vbTab & vbTab & TextBox5.Text & vbCrLf & _
vbTab & vbTab & TextBox6.Text, vbYesNo, "Confirm Details Entered.")

If Resp2 = vbYes Then
' Perform AddData Sub
AddData
End If
' Hide this form & unload
Me.Hide
Unload Me

End Sub

Sub AddData()

Dim strVal As String

' Work out what is the last row number under the `Doctors` Named Array.
LastRow = ThisWorkbook.Worksheets("Extra Tables").Range("Doctors").End(xlDown).Row + 1
' Go to last row and insert an entire row.
ThisWorkbook.Worksheets("Extra Tables").Range("F" & LastRow).Select
Selection.EntireRow.Insert
' Insert New Doctor Details
ThisWorkbook.Worksheets("Extra Tables").Range("F" & LastRow).Select
ActiveCell.Value = TextBox1.Text
ThisWorkbook.Worksheets("Extra Tables").Range("G" & LastRow).Select
ActiveCell.Value = TextBox2.Text
ThisWorkbook.Worksheets("Extra Tables").Range("H" & LastRow).Select
' TextBox3.Text (Address Line 1) has to have some value before getting to this point,
' so include it into strVal now.
strVal = TextBox3.Text
' Add remaining Address Lines are required.
If TextBox4.Text "" Then _
strVal = strVal & vbLf & TextBox4.Text
If TextBox5.Text "" Then _
strVal = strVal & vbLf & TextBox5.Text
If TextBox6.Text "" Then _
strVal = strVal & vbLf & TextBox6.Text
'Post Address Lines to ActiveCell
ActiveCell.Value = strVal

' SORT `DoctorsTable` Array
ThisWorkbook.Worksheets("Extra Tables").Range("F15:H" & LastRow).Sort _
Key1:=Range("F15"), Order1:=xlAscending, Key2:=Range("G15"), _
Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
' Rebuild Named Ranges that have been expanded.
ActiveWorkbook.Names.Add Name:="Doctors", _
RefersTo:="='Extra Tables'!$F$15:$F$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsArray", _
RefersTo:="='Extra Tables'!$F$15:$G$" & LastRow
ActiveWorkbook.Names.Add Name:="DoctorsTable", _
RefersTo:="='Extra Tables'!$F$15:$H$" & LastRow
End Sub

Private Sub TextBox1_Change()
' Ensure Doctors Name
If Len(TextBox1.Text) >= 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) > 0 Then
If Len(TextBox3.Text) > 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
MsgBox "Docors Names generally don't start with numbers.", _
vbOKOnly, "Incorrect Details !!"
TextBox1.Text = ""
TextBox1.SetFocus
ContinueButton.Enabled = False
End If
End Sub

Private Sub TextBox2_Change()
If Len(TextBox1.Text) >= 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) > 0 Then
If Len(TextBox3.Text) > 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
End Sub

Private Sub TextBox3_Change()

If Len(TextBox1.Text) >= 0 And IsNumeric(VBA.Left(TextBox1.Text, 1)) = False Then
If Len(TextBox2.Text) > 0 Then
If Len(TextBox3.Text) > 0 Then
ContinueButton.Enabled = True
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
Else
ContinueButton.Enabled = False
End If
End Sub

Private Sub Userform_Activate()
ClearText
TextBox1.SetFocus
ContinueButton.Enabled = False
End Sub
--------------------

---
Message posted from http://www.ExcelForum.com/

Hi,
So I have a column of cells that are the combination of a other cells in the row and a result of a few "if functions." The value comes out exactly as planned however in order for the data to be sent to the DDE server I need to press F2, go the beginning of the cell to insert an "=" sign and then press enter. I would like a macro to do what I just mentioned and then go down to the cell in the column. I used to write code using C++, but I'm a little rusty and unfamiliar with VB. Below is the formula and then the result of the cell. Any information or suggestions would be greatly appreciated!

=(IF(G10>0,"=ANVDdeSrv|"&B10&"!SendOrde r"&L10&"NYSE_"&ABS(G10)&"_1_100_"& amp;M10,IF(G10<0,"=ANVDdeSrv|"&B10&"!Se ndOrder"&L10&"NYSE_"&ABS(G10)&"_1_ 100_"&N10,)))

ANVDdeSrv|AMD!SendOrder_Sell_NYSE_300_1_100_3.3

Hi, here's a challenge beyond the capacity of my thinker!

I have the formulas below in the respective cells:
In cell E3 is: =INDEX(E$101:E$981,MATCH(I3,I$101:I$981,),)
In cell E4 is: =INDEX(E$101:E$981,MATCH(I4,I$101:I$981,),)
These continue in Col E down to Row 96. Col E is where the final calculations reside that I'm interested in.

These formulas work fine, but I'd like to add some functionality, and this is where it gets complicated for me.

In E2 is a date that I enter. From I2:Z2 are also dates one week apart, with the weekly data beneath these headings. So each column represents a weeks worth of data, headed by the Friday's date in Row 2 above each column. Column I is the most recent data, and I add a new column each week, inserting a new column so the most recent data is always in Col I.

The formulas above work fine as long at I'm only focused on Col I (the most recent data). But I would like to add the ability to the above formulas so that I can change the date in E2, and the column in focus will change instead from Col I, to the column with a date heading that matches E2.

E.g. If I change E2 to 23 Jan, I would like the column in focus to change from I (as per the present formula) and headed by 13 Feb, to Col L (headed by 23 Jan in L2). Changing this column of focus, will therefore change the final results that I want to see in Col E. I can easily do this manually by just changing the I to L in the above formulas, but would be nice if I can have it do it automatically, based on the date I type into E2.

I have tried to focus on ways to combine another Match, along with Substitute and Address, or Concatenate, to look for ways to change the I to L in this part of the forumula MATCH(I3,I$101:I$981,),) but I'm not getting anywhere.

Any thoughts on how to proceed with this would be much appreciated.

Thanks!
Harold

Hello,
I assume that there's an easy way to do this, and I just haven't found it
yet. I have a table with the headers in row 2, and data beneath. When new
data is added to the table, it is inserted in row 3, and the rest of the data
is pushed down one row. Unfortunately, when this happens, the chart "splits"
the source data range to only include the data that was there prior. Here's
an example:

Say my data table is in A2:B10, with the headers in row 2. The equation in
the "Source Data" field is

=SheetName!A2:B10

When new data is added, the data table becomes A2:B11, and the equation in
the source data field becomes

=Sheetname!A2:B2,Sheetname!A4:B11

What I'd like is for, after the new data is added, the equation to become

=Sheetname!A2:B11

I've tried anchoring parts of the table with "$", naming the range and I've
tried using the Indirect function, to no avail. The indirect function
example would be:

=Indirect("Sheetname!A2"):Sheetname!B10

This charts fine, but as soon as new data is added, Excel removes the
Indirect function and reverts to the split reference above. Same thing
happened when I used a named range. I could write a fairly simple VBA
routine that would update the source data, but I'd rather see if there's a
way to have the application do it automatically, if possible.

If anyone has any ideas, I'd certainly appreciate it.

Excel2000, WinXPPro, Chart is on a separate sheet, if that matters.

Thanks to all.

Mike

Hello all,

I have a column named Last Update, into which I want to be able to click
on a row and press a key combination and have today's date automatically
stamped in this cell.

I created a macro, it inserts "=today()" in the current active cell,
which is fine, I execute my macro and see today's date in the cell.
Problem is that when I look at that cell tomorrow, it will no longer
have yesterday's date in it.

Somehow I need the function to insert in that cell the value of the
formula, and not the formula itself, so's when I open the sheet in the
future, I will see in the cell the date when the macro was executed.

I'm fairly certain this is a very simple thing to do, but as anyone
who's tried to make use of Excel's help knows, there is very little
usable information here. I've searched the net, but always find how to
do everything else but what I specifically need.

I tried putting the =today() formula somewhere else in the sheet so as I
could copy and then paste the value of the formula only, but I need to
edit the macro so as it won't paste the value to the cell that was
active at the time I created the macro. If you're not familiar with VB,
this is no small feat.

To make matters MUCH WORSE, I was going to try recording the macro with
the "Relative" button depressed on the small Macro toolbar which appears
when I would be recording a macro, but I made the mistake of trying to
cancel a macro recording by clicking on the "X" close button of this
small toolbar. Now when I'm recording macros, I don't have this toolbar
pop up any more, along with the "Relative" button that was on this
toolbar. Anyone of you daring enough to try this in order to attempt to
find this little Macro toolbar over again? Careful. I've looked all
over the "Customize" window, but cannot find this "Relative" button
anymore. Where is it???

Any assistance is greatly appreciated. Thank you.

Dear all,

I have a data tape with is filtered based on a criteria and is copied to
another separate sheet. On this staging sheet I use offset and counta
functions to dynamically set a range which I use for a chart. Every time the
data changes the range changes as I want.

On the chart file I insert the name of the range to the data source field
ie. Sheet1!Rangename then it displays the data correctly. however if the
range expands to 5 rows from the initial 4 it still displays the initial 4.
I once read that you needed to create a vba code to make sure that ever time
it is run it resets the named range. Can you help?

Hi guys, I have a macro which it runs successfully in MS office 2003. But when I run it on MS office 2007,there is some error. Below are my codings. Can someone please advice whats wrong with it?
The one with bold is the highlighted error.

It has the following error message when i run it.
"Run-time error '1004':
Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and columns than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use Copy and paste commands to insert it into the sheets of another workbook."


	VB:
	
 
Option Explicit 
 
Public Const RoNumber As Integer = 80 
Public Const Max_Data As Integer = 45 
Public Const Max_RoPerGroup As Integer = 8 
 
Public ro(RoNumber) As String 
Public roPath(RoNumber) As String 
Public lot As String 
Public numParameter As Integer 
Public numDevice As Integer 
Public paraName As String 
Public roCount As Integer 
Public skipParameter(300) As Integer 
Public parameter(300) As String 
 
Public roToOpen As Variant 
Public groupNum As Integer 
Public groupName(RoNumber) As String 
 
 
Sub CloseBook() 
     ' Close workbook without Saving dialog box.
    Application.DisplayAlerts = False 
    ActiveWorkbook.Close 
    Application.DisplayAlerts = True 
End Sub 
 
Public Function openROs() As Boolean 
     ' Open all ROs.
    Dim wBook As Workbook 
    Dim i As Integer 
    Dim roCounter As Integer 
     
     ' Open files.
    roCount = 0 
    roToOpen = Application.GetOpenFilename(MultiSelect:=True) 
    If IsArray(roToOpen) = False Then 
        Exit Function 
    End If 
     
    roToOpen = sortArray(roToOpen) 
    roCounter = 0 
    groupNum = 1 
     
    For i = 1 To UBound(roToOpen) 
        Workbooks.Open Filename:=roToOpen(i) 
         
        [B][I]ActiveWorkbook.Sheets(1).Move after:=ThisWorkbook.Sheets(1)[/I][/B] 
        roCount = roCount + 1 
        ThisWorkbook.ActiveSheet.Name = roCount 
        ThisWorkbook.Sheets("" & roCount).Tab.ColorIndex = groupNum 
         
         ' Register opened file.
        roPath(roCount - 1) = roToOpen(i) 
        ro(roCount - 1) = Strings.Mid$(roToOpen(i), Strings.InStrRev(roToOpen(i), "") + 1) 
        groupName(roCount - 1) = Strings.Left$(ro(roCount - 1), (Strings.InStrRev(ro(roCount - 1), "_", , vbTextCompare) -
1)) 
         
        roCounter = roCounter + 1 
        If roCounter = 8 And (i + 1) < UBound(roToOpen) Then 
             ' One Group is detected.
            groupNum = groupNum + 1 
            roCounter = 0 
        End If 
         
        ThisWorkbook.Sheets("CGP Menu").cells(10 + roCount, 2) = roCount 
        If roCount Mod 8 = 1 Then 
            ThisWorkbook.Sheets("CGP Menu").Select 
            ThisWorkbook.ActiveSheet.cells(10 + roCount, 2).Select 
             
            Selection.Font.Bold = True 
        End If 
        ThisWorkbook.Sheets("CGP Menu").cells(10 + roCount, 3) = ro(roCount - 1) 
    Next i 
     
    ThisWorkbook.Sheets("CGP Menu").cells(10, 2) = "Sequence" 
    ThisWorkbook.Sheets("CGP Menu").cells(10, 3) = "Registered Files" 
     
     ' Close workbooks.
    For Each wBook In Workbooks 
        For i = 0 To roCount - 1 
            If wBook.Name  ThisWorkbook.Name And wBook.Name = ro(i) Then 
                wBook.Close savechanges:=False 
                Exit For 
            End If 
        Next i 
    Next wBook 
    openROs = True 
    ThisWorkbook.Sheets("CGP Menu").Select 
     
    Exit Function 
     
getCancel: 
    openROs = False 
    Exit Function 
End Function 
 
Public Sub clearAllSheets(exceptionSheet1 As String, exceptionSheet2 As String, exceptionSheet3 As String) 
     ' Delete all sheets except "exceptionSheet".
    Dim toBeDelete As Object 
     
     ' Turn off automatic calculation to speed up killing Excel sheets.
    With Application 
        .Calculation = xlManual 
        .MaxChange = 0.001 
    End With 
    ActiveWorkbook.PrecisionAsDisplayed = False 
     
     ' Delete the sheets.
    For Each toBeDelete In ThisWorkbook.Sheets 
        If toBeDelete.Name = exceptionSheet1 Or toBeDelete.Name = exceptionSheet2 Or toBeDelete.Name = exceptionSheet3 Then 
             ' Do nothing.
        Else 
            Application.DisplayAlerts = False 
            toBeDelete.Delete 
            Application.DisplayAlerts = True 
        End If 
    Next toBeDelete 
     
     ' Turn on automatic calculation for next jobs.
    With Application 
        .Calculation = xlAutomatic 
        .MaxChange = 0.001 
    End With 
    ActiveWorkbook.PrecisionAsDisplayed = False 
End Sub 
 
Public Function checkGroup() As Boolean 
     ' Check group information.
    Dim fileCounter As Integer 
    Dim roCounter As Integer 
    Dim oSheet As Worksheet 
    Dim deviceSignature As String 
    Dim packageSignature As String 
     
    checkGroup = True 
    deviceSignature = ThisWorkbook.Sheets("1").cells(3, 1).Value 
    packageSignature = ThisWorkbook.Sheets("1").cells(4, 1).Value 
     
    For fileCounter = 1 To roCount 
        With ThisWorkbook.Sheets("" & fileCounter) 
            If .cells(3, 1).Value  deviceSignature Or .cells(4, 1).Value  packageSignature Then 
                checkGroup = False 
                Exit Function 
            End If 
        End With 
         
        If fileCounter Mod 8 = 0 And (fileCounter + 1 < roCount) Then 
             ' Update devideSignature and packageSignature for new group.
            deviceSignature = ThisWorkbook.Sheets("" & (fileCounter + 1)).cells(3, 1).Value 
            packageSignature = ThisWorkbook.Sheets("" & (fileCounter + 1)).cells(4, 1).Value 
        End If 
    Next fileCounter 
End Function 
 
Public Sub tabulateValue() 
     ' Tabulate value of each group.
    Dim groupCounter As Integer 
    Dim currentGroupName As String 
    Dim roCounter As Integer 
    Dim startPageOfGroup As Integer 
    Dim data(Max_Data, Max_RoPerGroup) As Double 
    Dim data1(Max_Data, Max_RoPerGroup) As Double 
    Dim data2(Max_Data, Max_RoPerGroup) As Double 
    Dim data3(Max_Data, Max_RoPerGroup) As Double 
    Dim dataCounter As Integer 
    Dim rowCounter As Integer 
    Dim rowOffset As Integer 
    Dim roArrayCount As Integer 
    Dim ColCounter As Integer 
    Dim offset1 As Integer 
    Dim offset2 As Integer 
    Dim offset3 As Integer 
    Dim averageValue As Double 
    Dim averageValueRow As Integer 
    Dim averagevalueCol As Integer 
     
    rowOffset = 0 
    For groupCounter = 1 To groupNum 
        currentGroupName = groupName((groupCounter - 1) * 8) 
        ThisWorkbook.Worksheets.Add after:=Sheets("CGP Menu") 
        ThisWorkbook.ActiveSheet.Name = currentGroupName 
        startPageOfGroup = (groupCounter - 1) * 8 + 1 
         
        If Strings.InStrRev(CStr(ThisWorkbook.Sheets("" & startPageOfGroup).cells(4, 1).Value), ",", , vbTextCompare)  1 Then

                         ' Copy data into the specific array.
                        dataCounter = 0 
                        For rowCounter = 1 To Max_Data 
                            Select Case (rowCounter Mod 3) 
                            Case 1 
                                data1(dataCounter, roArrayCount) = .cells((rowOffset + rowCounter), 2).Value 
                            Case 2 
                                data2(dataCounter, roArrayCount) = .cells((rowOffset + rowCounter), 2).Value 
                            Case 0 
                                data3(dataCounter, roArrayCount) = .cells((rowOffset + rowCounter), 2).Value 
                                dataCounter = dataCounter + 1 
                            End Select 
                        Next rowCounter 
                    End If 
                End With 
                roArrayCount = roArrayCount + 1 
            Next roCounter 
             
             ' ===================================================== Part B: Tabulate data.
            offset1 = 2 
            offset2 = offset1 + (Max_Data / 3) + 4 
            offset3 = offset2 + (Max_Data / 3) + 4 
            With ThisWorkbook.Sheets(currentGroupName) 
                .Select 
                .cells(offset1, 2).Resize((Max_Data / 3), Max_RoPerGroup) = data1 
                .cells(offset2, 2).Resize((Max_Data / 3), Max_RoPerGroup) = data2 
                .cells(offset3, 2).Resize((Max_Data / 3), Max_RoPerGroup) = data3 
                 
                .cells(offset1, 1).Value = 1 
                .cells(offset1 + 1, 1).Value = 4 
                .cells(offset1, 1).Resize(2, 1).Select 
                Selection.AutoFill Destination:=.cells(offset1, 1).Resize((Max_Data / 3), 1), Type:=xlFillSeries 
                 
                .cells(offset2, 1).Value = 2 
                .cells(offset2 + 1, 1).Value = 5 
                .cells(offset2, 1).Resize(2, 1).Select 
                Selection.AutoFill Destination:=.cells(offset2, 1).Resize((Max_Data / 3), 1), Type:=xlFillSeries 
                 
                .cells(offset3, 1).Value = 3 
                .cells(offset3 + 1, 1).Value = 6 
                .cells(offset3, 1).Resize(2, 1).Select 
                Selection.AutoFill Destination:=.cells(offset3, 1).Resize((Max_Data / 3), 1), Type:=xlFillSeries 
                 
                For ColCounter = 0 To (roArrayCount - 1) 
                    .cells((offset1 - 1), (2 + ColCounter)).Value = Strings.Mid$(roToOpen(startPageOfGroup + ColCounter),
Strings.InStrRev(roToOpen(startPageOfGroup + ColCounter), "_") + 1) 
                    .cells((offset2 - 1), (2 + ColCounter)).Value = Strings.Mid$(roToOpen(startPageOfGroup + ColCounter),
Strings.InStrRev(roToOpen(startPageOfGroup + ColCounter), "_") + 1) 
                    .cells((offset3 - 1), (2 + ColCounter)).Value = Strings.Mid$(roToOpen(startPageOfGroup + ColCounter),
Strings.InStrRev(roToOpen(startPageOfGroup + ColCounter), "_") + 1) 
                Next ColCounter 
            End With 
        End If 
    Next groupCounter 
     
End Sub 
 
Public Sub tabulateParameter() 
     ' Tabulate parameter by parameter of all ROs.
    Dim paraCounter As Integer 
    Dim i As Integer 
    Dim paraCount As Integer 
     
    For paraCounter = 1 To numParameter 
        ThisWorkbook.Worksheets.Add after:=Sheets("Percentage Settings") 
         
        With ThisWorkbook.ActiveSheet 
             ' Check parameter name for not containing : /  ? * [ ], and replace with _.
            paraName = Strings.Replace(ThisWorkbook.Sheets("" & 1).cells(3 + paraCounter, 4), ":", "_") 
            paraName = Strings.Replace(paraName, "/", "_") 
            paraName = Strings.Replace(paraName, "", "_") 
            paraName = Strings.Replace(paraName, "?", "_") 
            paraName = Strings.Replace(paraName, "*", "_") 
            paraName = Strings.Replace(paraName, "[", "_") 
            paraName = Strings.Replace(paraName, "]", "_") 
             
             ' Check repeated parameter.
            For paraCount = 0 To paraCounter - 1 
                If paraName = parameter(paraCount) And paraCount + 1  paraCounter Then 
                    paraName = paraName & "Repeat_" & paraCount & "_" & paraCounter 
                End If 
            Next paraCount 
             
            parameter(paraCounter - 1) = paraName 
             
            .Name = paraName 
            .cells(1, 1).Value = "Source Data" 
            .cells(2, 2).Value = 1 
            .cells(2, 2).AutoFill Destination:=.cells(2, 2).Resize(1, roCount), Type:=xlFillSeries 
             
            .cells(3, 1).Value = 1 
            .cells(3, 1).AutoFill Destination:=.cells(3, 1).Resize(numDevice, 1), Type:=xlFillSeries 
             
            For i = 1 To roCount 
                ThisWorkbook.Sheets("" & i).cells(3 + paraCounter, 6).Resize(1, numDevice).Copy 
                ThisWorkbook.Sheets(paraName).cells(3, 1 + i).Resize(numDevice, 1).PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _ 
                False, Transpose:=True 
            Next i 
             
             ' Calculate drift.
            Call calculateDrift 
        End With 
    Next paraCounter 
End Sub 
 
Public Sub calculateDrift() 
     ' Calculate percentage drift by pasting formula.
    Dim tempAddress As String 
     
     ' Fill source calculation.
    With ThisWorkbook.Worksheets(paraName) 
         '.Cells(1, "blank cell + 1 " + "two times" * roCount).Value = "Drift Percentage"
         ' Fill device series.
        .cells(1, 4 + 2 * roCount).Value = "Source Calculation" 
        .cells(2, 4 + 2 * roCount).Value = 1 
        .cells(2, 4 + 2 * roCount).AutoFill Destination:=.cells(2, 4 + 2 * roCount).Resize(1, roCount), Type:=xlFillSeries 
         
         ' Set 1 as reference (100%).
        .cells(3, 4 + 2 * roCount).Value = 100 
        .cells(3, 4 + 2 * roCount).AutoFill Destination:=.cells(3, 4 + 2 * roCount).Resize(numDevice, 1), Type:=xlFillValues 
         
         ' roCount is test >1 and >2 due to range resize issue.
        If roCount > 1 Then 
            .cells(3, 5 + 2 * roCount).Value = "=PRODUCT(SUM(C3,-$B3),100/$B3)" 
            .cells(3, 5 + 2 * roCount).AutoFill Destination:=.cells(3, 5 + 2 * roCount).Resize(numDevice, 1),
Type:=xlFillDefault 
            If roCount > 2 Then 
                .cells(3, 5 + 2 * roCount).Resize(numDevice, 1).AutoFill Destination:=.cells(3, 5 + 2 *
roCount).Resize(numDevice, roCount - 1), Type:=xlFillDefault 
            End If 
        End If 
    End With 
     
     ' Fill percentage.
    With ThisWorkbook.Worksheets(paraName) 
        .cells(1, 3 + roCount).Value = "Drift Percentage" 
        .cells(2, 3 + roCount).Value = 1 
        .cells(2, 3 + roCount).AutoFill Destination:=.cells(2, 3 + roCount).Resize(1, roCount), Type:=xlFillSeries 
         
        tempAddress = .cells(3, 4 + 2 * roCount).Address(False, False) 
        .cells(3, 3 + roCount).Value = "=IF(ISERR(" & tempAddress & "),IF($B3=0,0,1000),IF(" & tempAddress & ">1000,9999,IF("
& tempAddress & "

Hello,

I have 50 spreadsheets of addresses that all need to be converted from a single column of data to 5 columns. Luckily, all of the data is in the same format:

Column 1
Name
Address
City State Zip
Phone
Info
Name
Address
City State Zip
Phone
Info
Etc...with no spaces

I need to make the above data, that is currently in 1 column, into 5 columns to do a mail merge:

Row 1: Name, Address, City State Zip, Phone, Info (5 columns)
Row 2: Name, Address, City State Zip, Phone, Info

I need someone to write or add a macro to the attached excel spreadsheet that will transpose the addresses in the single column into 5 columns. I also need instructions on how to insert and run the macro in my other spreadsheets to perform the same function.

A million thank yous!

ID Golf Course Addresses.xlsx

Hi All

I have a change event macro in a specific sheet module. I inserted a PageNumber UDF in a general module.

However, the UDF stops the change event from working?? When I remove the UDF code, the change event works again.

Is there a reason for this?

Below is the code:


	VB:
	
 
In sheet 1 module: 
 
Private Sub Worksheet_Change(ByVal target As Excel.Range) 
    If Not Intersect(target, Range("G7")) Is Nothing Then 
        With Range("G7") 
            Range("8:12").EntireRow.Hidden = .Value = "No" 
            Range("14:17").EntireRow.Hidden = .Value = "Yes" 
            Range("22:23").EntireRow.Hidden = .Value = "Yes" 
        End With 
    End If 
    If Not Intersect(target, Range("G14")) Is Nothing Then 
        With Range("G14") 
            Range("22:23").EntireRow.Hidden = .Value = "Distributor" 
            Range("16:21").EntireRow.Hidden = .Value = "End user" 
        End With 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In general module 1:


	VB:
	
 
Public Function PageNumber( _ 
    Optional ByRef target As Excel.Range, _ 
    Optional ByVal nStart As Long = 1&) As Variant 
    Dim pbHorizontal As HPageBreak 
    Dim pbVertical As VPageBreak 
    Dim nHorizontalPageBreaks As Long 
    Dim nPageNumber As Long 
    Dim nVerticalPageBreaks As Long 
    Dim nRow As Long 
    Dim nCol As Long 
     
    On Error Goto ErrHandler 
    Application.Volatile 
    If target Is Nothing Then _ 
    Set target = Application.Caller 
    With target 
        nRow = .Row 
        nCol = .Column 
        With .Parent 
            If .PageSetup.Order = xlDownThenOver Then 
                nHorizontalPageBreaks = .HPageBreaks.Count + 1& 
                nVerticalPageBreaks = 1& 
            Else 
                nHorizontalPageBreaks = 1& 
                nVerticalPageBreaks = .VPageBreaks.Count + 1& 
            End If 
            nPageNumber = nStart 
            For Each pbHorizontal In .HPageBreaks 
                If pbHorizontal.Location.Row > nRow Then Exit For 
                nPageNumber = nPageNumber + nVerticalPageBreaks 
            Next pbHorizontal 
            For Each pbVertical In .VPageBreaks 
                If pbVertical.Location.Column > nCol Then Exit For 
                nPageNumber = nPageNumber + nHorizontalPageBreaks 
            Next pbVertical 
        End With 
    End With 
    PageNumber = nPageNumber 
ResumeHere: 
    Exit Function 
ErrHandler: 
    PageNumber = CVErr(xlErrRef) 
    Resume ResumeHere 
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi all,

I am new to VBA and to the forum. I have a project to piece together using VBA but can only implement it one step at a time. I am only familiar with formulas and not the VBA equivalents so excuse my language. The first step requires:

1. Open "Spreadsheet A"
2. Filter by Column B (title- Company), for Data "Company F". There will only be 1 row.
3. Copy value in Column M for the 1 result from the filter.
4. Set print area to Cell "A1" to Cell "M2" (Row A- Title Row, Row B-result of filter) (I only want to print the title row and the single row resulting from the filter. Close "Spreadsheet B"
5. Open "Spreadsheet B"
6. Copy Tab "1", place at end, name "Oct 5" (current date).
7. Insert current date in Cell "B2" (would like static date, not =now() function)
8. Paste copied data from Step 3 above to Cell "C5"
9. Set print area from Cell "A1" to Cell "P10". Print
10. Save.

Please forgive if I left out any additional information needed, and thanks in advance for the help.

Chris

Hi All,

I'm very new to using VBA within Excel (have only done a bit of VB5 a couple of years ago), but have a project I'm working on that requires me to use some VBA.

The basics of what I want to do are as follows:
- I have a worksheet with multiple rows of data that I want to process and insert into spreadsheet 1 of a new workbook.
- The processing (much of which seems to be working OK already individually) involves pulling out certain text and numbers from a text string in a cell, checking for < signs for values in other cells and assigning true/false, etc.
- The source spreadsheet has 14 columns while the target spreadsheet has to have 26 columns populated from data in the source spreadsheet (for instance, there are 9 columns that I have to determine whether the < sign exists and assign true/false [goes into 1 column as true or false] and then I insert the number value without the < sign into a second column)
- So, if the source spreadsheet has 15 rows, I need to loop through the cells in each row, perform actions on each cell, then add to the new spreadsheet, until each row has been processed and I end up with 15 rows of updated values in the new spreadsheet. I know which column each piece of data needs to be inserted into, but the row number will change.

So, some of the things I'm not sure how to do are:
1. how to reference the worksheets in different workbooks, so that the correct source data are used and the target destination is correct. To ensure that the correct worksheet is used, it also might be useful to use a function that returns the workbook and spreadsheet name, that can be referenced throughout the rest of the procedures.
2. I can't use specific references to individual cells, but need to loop through each cell in each row (or should I do it column by column?)
3. For the used cell range I am currently using a function that returns the number of rows and columns in the source spreadsheet, which I want to then use to ensure that the correct number of rows and colums are referenced from the source spreadsheet, and inserted into the target spreadsheet.

I am currently using Excel 2002, but the code needs to be compatible with Excel 2000, and possibly Excel 97 also. From what I've read, most VBA code written in 2002 is compatable with version 2000, and much will also work in 97.

Anyway, if anyone has any pointers on some or any of the above, that would be great.

Thanks :D