Re: Translate Column Name to Column Number
Re: Translate Column Name to Column Number
I am selecting an arbitrary range via an InpuBox.
i.e.: Set rng = Application.InputBox(prompt:="Select a Range", Type:=8)
I need to extract the starting row number and the column number of that random selection.
The row number I got easily, and for the column I managed to extract the 'Name', i.e. the letter "E". How to I convert this into the column 'number', i.e. 5?
I have found a rather clumsy way:
Select Case col
c = 1
c = 2
c = 3
and so on.....
This results in rather long code and I would like to improve/simplify on this.
Any suggestions, please?
I want to choose the area to copy, thus I use the following:
sel = InputBox("Enter the Range in 'A1 : D5' Format", "Enter the Cell Range You Want to Copy")
ActiveSheet.Range("" & sel & "").copypicture xlScreen, xlPicture
For Each sht In ActiveWorkbook.Sheets
Ampersand = Chr(38)
Quote = Chr(34)
FooterFormat = "Arial"
FooterTextSize = "16"
FooterText = ActiveWorkbook.FullName
Formatting = Ampersand & Quote & FooterFormat & Quote & Ampersand & FooterTextSize
FOOTER = Formatting & FooterText
sht.PageSetup.LeftFooter = FOOTER
Just to check:
have you put the code into "ThisDocument" instead of a module?
This made the difference for me, as the code would NOT execute from within a module.
The following works on my system with O2k on Win2K:
Dim xlapp As Object
Dim CheckReqeust As Object
Set xlapp = CreateObject("Excel.Application")
Set CheckRequest = xlapp.Workbooks.Add("c:\temp\prc.xlt")
xlapp.Visible = True
Set CheckRequest = Nothing
Set xlapp = Nothing
I have placed the code not into a module but into "ThisDocument"
2 questions please:
-you say "...tab into the combobox" . What do you want to happen when you 'tab into it'?
-how do you want to 'trigger' your date-entry in Col D ? I assume that for each row of parts you may have to go back at some stage and re-selct from Col C. Do you want to update your date at that point or...?
you are right, of course.
I missed that part and had based my solution on ordinary AutoShapes.
I have tried both yours and Colo's code and I can not get them to work, i.e. non of the shapes seem to get selcted.
What am I missing?
Thanks for your input.
I am running O2k on Win2k.
I have a (tested) solution for you, where the font is based on a reference cell (i.e. A1)
.Name = Range("a1").Font.Name
.FontStyle = Range("a1").Font.FontStyle
.Size = Range("a1").Font.Size
.ColorIndex = Range("a1").Font.ColorIndex
If you require, you can add more font properties into the 'with' - 'end with' section, such as
if you want a bit more flexibility with regards to the sheetnames and 'wildcards' you use you could use the following (tested) macro:
Dim SheetNames() As String
Dim SheetCount As Integer
Dim i As Integer
Dim wildcard As String
Dim length As Integer
wildcard = InputBox("Type in 'wildcard' characters for the sheets you want to delete", "Deletion of Worksheets")
length = Len(wildcard)
Application.DisplayAlerts = False
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
For i = 1 To SheetCount
If Left(SheetNames(i), length) = wildcard Then
Application.DisplayAlerts = True
Best Regards, Manfred
from your request it is not entirely clear how you want to print ALL formulas.
I assume you are referring to formulas in one of your worksheets/books.
a) you could switch to 'formula view' as described in the posting from Hans or, a slightly faster way is to use Ctrl+~. Once you see your sheet with the formulas (and unformatted numbers) you can print it.
b) if you want to search your document for formulas only (via VBA) and then print out all the formulas in a listing (on a newly created sheet) you could use the attached macro.
First select a range you want to investigate (or the whole sheet), then run macro.
Dim counter As Integer
Dim i As Variant
Dim sourcerange As Range
Dim destrange As Range
Dim curSheet As String
curSheet = ActiveSheet.Name
Set sourcerange = Selection.SpecialCells(xlFormulas)
ActiveSheet.Name = "test"
Set destrange = Sheets("test").Range("b1") 'Substitute your range here
destrange.Value = "Address"
destrange.Offset(0, 1).Value = "Formula"
If Selection.Count > 1 Then
For Each i In sourcerange
counter = counter + 1
destrange.Offset(counter, 0).Value = i.Address
destrange.Offset(counter, 1).Value = "'" & i.Formula
ElseIf Selection.Count = 1 And Left(Selection.Formula, 1) = "=" Then
destrange.Offset(1, 0).Value = Selection.Address
destrange.Offset(1, 1).Value = "'" & Selection.Formula
MsgBox "This cell does not contain a formula"
if your time can go past midnight you need to allow for a 'roll-over'.
You can use the example as per attached sheet.
The spreadsheet U just sent through has a number of problems built into the 'green' area (c16:n18). Because U moved the 'yellow' area from (c6:n8) to (c8:n10) the formulas in (d16:n18) are refering to the wrong area, thus the results are wrong. In the attached sheet (cashflow3.xls) I have fixed this.
If you check the 'light blue' area (c35:n35) you will see that the results you want (in this instance for Plat) are EXACTLY the values I already had in my previous sheet - as well as in this sheet (c16:n16).
If you analyse the formulas ('green' area) you will notice that they are already accumulative, i.e. they do exactly what you are doing with your table in c22:n33.
Please refer to the colored-in areas in the attached sheet for further explanation.
Any hassels, pls ask.
Best regards, Manfred
In the attached sheet pls check the following:
- the green range (C17:N20) contains the formulas you are after. You just need to multiply the monthly fees for each month and ADD the fees from the previous month
- there are a number of errors in your formulas, pls check the areas highlited in orange. I have fixed them, but pls check.
Any further questions, please contact me.
Sorry, somehow the attachenment did not get attached.
it took me quite a while to get this one right.
The main complication was that there are 2 off '0'-values at the bottonm right of the table and that created all sort of problems when interpolating close to the max power or max speed range.
However, I think I ironed out those bugs and the model -seems to- work fine. Any hassels, let me know.
I have attached your spreadsheet with the solution to Question 1.
Q2 is much more elaborate as you have non-linear relationships between pulley diameter and shaft speed as well as pulley diameter and design power.
I'll have a look at it anyhow.
Sorry to sound 'thick' but there are a few things I still don't understand:
-In your first post you refer to a 'setup fee' and an 'ongoing monthly payment'. I can not see in the worksheet where you capture 'setup fees' and where the 'monthly cost'. I would assume that those costs are different and thus need a seperate place to be captured.
- in your model I can not see any facility that allows to see/track/capture when someone joins or how many joined in one month
-if, in your revenue section c6:c8, there are 1Plat, 1Gold, 1Silver unit, does this mean that 3 have joined in that month?
- your section 'unit cost': does it refer to either 'setup fee', 'monthly cost', or both or something else?
- in your last posting: could you please elaborate on the example of 3 people joining.. What are all the '100' - I assume they are not 'unit costs' as for Gold it would be 75 and for Silver=50
- in the same example, how can one see who (i.e. how many) signs up for what and when?
- how do you arrive at the 100-200-300-300 set of numbers?
Sorry for the many questions, but I would like to understand in order to be of help.
what are U counting in D21?
I only get a result for numbers, any text is ignored.
Your countif formula seems to be OK, i.e. the syntax works with my sample data