Posts by StefanG

Re: Splitting A Number Into Segments

Hi Ian,
I like your lookup option.

Quote

=LOOKUP(A2,B2:B5,D2:D5)*A2

Nice and short - sweet.
I'd done this a bit longer once again, so i liked your solution.
=IF(A1<11,D2,IF(A1<=15,D3*A1,IF(A1<16,0,IF(A1<=20,D4*A1,IF(A1>20,D5*A1)))))

I suppose that triste will have to change the reference from A2 to A1 in your formula in F2 unless s/he had a typo.

Quote

so I need that uumber in cell (a1) to be extracted acoording to that role and give me the final result

Stefan

Re: Splitting A Number Into Segments

Hi triste,
I'm sure there are better and shorter solutions.

Say you have the Names of your loan officers in column A and their case numbers or so in column B - so in column A you could find the name(s) of loan officers multiple times, once for each case processed.

Place this in C1
=COUNTIF(A:A,"Name") - where Name will need to be replaced with the name of a Loan Officer. Copy the formula down and add all other respective Names.

Place this in D1 (and copy down as needed as well).
=IF(C1<11,0,IF(C1<=15,6,IF(C1<16,0,IF(C1<=20,8,IF(C1>20,10)))))

Not sure about your calculation part.
If one has, say 20 cases, will the payout be 20x8? If that, I'd add another cell to the right and do
=Sum(C1xD1) (copy down once again)

Stefan

Re: Inputbox Data Entry Format

Hi DISCARNET,

How about:

vAnswer = Application.InputBox ...


Range("PlateNumbers") = Format(vAnswer, "@@@@ @@@")
Range("PlateNumbers").Value = UCase(Range("PlateNumbers"))

Stefan

Re: Copy Form Fields To Insert As Two Cells

Hi Nori,
The application is SAP.

When you select and copy A1 and B1
A1 = ValueA1
B1 = ValueB1
then select E3 and paste, the data will paste into E3 and F3.
E3 = ValueA1
F3 = ValueB1

When you select and copy TextBox1 (which holds the data from A1 & B1)
TextBox1 = ValueA1ValueB1
then select E3 and paste, the data will paste into E3 only.
E3 = ValueA1ValueB1

Instead, i want to select and copy TextBox1 (which holds data from A1 & B1)
TextBox1 = ValueA1ValueB1
then select E3 and paste, the data will paste into E3 and F3.

I am wondering how this can be accomplished, i.e. by using a "special character" between the ValueA1 and ValueB1 in TextBox1. Apparently you cannot copy two TextBoxes at one time... or can you?

I'm sure this is clear as mud.
Thank you for your patience and trying to help.

Stefan

Re: Copy Form Fields To Insert As Two Cells

Hi,
Sorry, not my day to be clear i suppose, my apologies.

I have a UserForm with two TextBoxes.
Textbox1 and Textbox2 are next to each other on the UserForm and contain data from Sheet1 cells A1 and E1.
The data is filled in upon activating the UserForm...

Private Sub UserForm_Activate()
TextBox1.Text = Range("Num1")
TextBox2.Text = Range("Qty1")
...

One of the tasks is to use the data in TextBox1 and TextBox2 in another application.

I can copy TextBox1 by itself and then TextBox2; i would like to be able to copy both at once.

If i combine that data that is now TextBox1 and TextBox2 into one TextBox I could ... like so,

TextBox1.Text = Range("Num1") & Range("Qty1")

but I dont know what "divider" to use so that the copied data behaves as if I copy two cells in a Worksheet.

Say I copy A1 and B1. Copying cells like that allows pasting both at the same time into the other location/application.

I hope this makes more sense with the appropriate terminology.
Stefan

Hi,
Is there a way to copy data from one form text field so that it will be pasted into two cells? (this paste will ultimately go into a different, non MS, application.)

I tried i.e. chr(10) when the data is filled into the form but that is unsuccessful. Is there a "special character" that would result in the pasted data to be read as two cells?

The data in the form field is usually 6-digits (numbers), a dash and then 1 or two more digits (numbers), i.e. 123456 - 1 or 987654 - 12. We coudl omit the dash and enter any sort of divider though.

Or...
Do you know of an option that would allow to copy two form text fields at one time to achive the above?

Stefan

Re: Insert Character

Hi Macropheliac,

Sub Insert_Character()
    Select Case Len(ActiveCell)
    Case Is = 3
        ActiveCell.Value = Left(ActiveCell, 2) & "X" & Right(ActiveCell, 1)
    Case Is = 4
        ActiveCell.Value = Left(ActiveCell, 1) & "X" & Right(ActiveCell, 3)
    End Select
End Sub

Stefan
p.s.
What if the cell contained 3 characters and you run the code to insert X and you run it again, it will now see four characters and insert another X. Desidered?

Re: Down One Row

Hi Mike,
Have a look at xlUp, i.e. in this thread where i got some assistance.
Stefan

Re: Search Multiple Text Boxes For A String

Hi Grint,
Maybe this ("outside") thread will help some?
Stefan

Re: Count Unique Records With Multiple Criteria With Formula

Okay. I believe i figured out how the provided formulas work and how I can expand on these.
Unless i am mistaken, and the results which show to be correct, i can just add portions of IF's like "If(H2:H100>=A1" in the first part and the second part of the formula and add the respective amount of closing ")".

Out of interest, is there any limitation with these formulas?

Stefan

Re: Count Unique Records With Multiple Criteria With Formula

Quote from Domenic

Hope this helps!

Domenic,
Thank you so much - this is what I was looking for.
I can’t believe though, that I forgot to include an important factor. May I please trouble your expertise on this one more time please? Please accept my sincere apologies for this faux pa.

In addition to the given requirements, [COLOR="blue"]there is one more[/COLOR].
Count unique records in Column B where ...
1.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H >=A1 and <=A2

2.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... [COLOR="Blue"]Column C =A3[/COLOR] and Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
[COLOR="blue"]A3 - customer code A (and later through A11 respectively B, C, D, E, F, G, H, I)[/COLOR]
Column B - 6 digit number (or blank)
[COLOR="blue"]Column C - customer code (A, B, C, D, E, F, G, H or I)[/COLOR]Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

Thank you again,
Stefan

p.s.
I will also have a look into the add-in you suggested.

Okay, while i dont think that it will make any difference, i will mentioned that the references A1-A11 are on another sheet within the same workbook, the respective formulas will then be besides the customer code references.

___________
Edit: previously posted to wrong reply / added color reference

Re: Count Unique Records With Multiple Criteria With Formula

Batman,
Thank you for the sample. I will look this over. DCOUNT etc scares me even more then the other formula approach and I will try Domenics formulas first.
Thank you for your time and suggestion, i may be back.
Stefan

Edit: to provide answer to/for the right poster

Hi,
I'm looking for a formula approach for these please.

Count unique records in Column B where ...
1.)
... Column H >=A1 and <=A2

2.)
... Column H <>"" and Column I <>"expired" and <>"" and Column I >=A1 and <=A2

3.)
... Column H <>"" and Column I ="expired" and Column H+120 >=A1 and <=A2

4.)
... Column H <>"" and Column I >=A1 and <=A2 and Column J =""

A1 - user defined (start) Date 1
A2 - user defined (end) Date 2
Column B - 6 digit number (or blank)
Column H - Date 3 (or blank)
Column I - Date 4 (or "expired" or blank)
Column J - Date 5 (or blank)

Thank you,
Stefan

Re: Hyperlink &amp; Non-Hyperlinked Text in Same Cell

Hi Dave,
When i add the hyperlink in step 2, the font in that cell is blue/underlined. When i copy it, and then Shift-Edit-Paste Picture, it shows in black and white. - The only thing strange and "not working" is that the picture does not represent the colors of the copied cell. Otherwise performs as advertised.
Stefan

p.s.
In fact, any text color-formatted cell gets pasted in black and white, not in color.

Re: Hyperlink &amp; Non-Hyperlinked Text in Same Cell

Hi Dave,

Quote

Did you try it?

Sure did, several different ways. I attached a sample. I cant seem to achieve with your steps what is shown in row 5.
Stefan

Re: Hyperlink &amp; Non-Hyperlinked Text in Same Cell

Hi Dave,

Quote

Here is another work-around....
2) In another column AND cell Enter Click Here and create a Hyperlink (not with HYPERLINK function) Size this column so it just fits.
3) With this cell (step 2) Active copy it. Select other cell (step1) hold down Shift and go to Edit>Paste Picture. Move in place within the cell, far right.

This is interesting. I have some questions please.

Why, if a hyperlink is added to the cell in step 2) lets call it A2 and the text font is blue/underlined, does it not insert under 3) an image that shows the same blue/underlined font but a black and white picture? Did you mean to select 'Paste Picture Link' which does seem to give a true (colored) picture of A2.

I found that, while using 'Paste Picture Link' i cannot delete A2 as it would render the picture link with an empty box (but keep the hyperlink) and using the 'Paste Picture' allows to delete A2 maintains the picture (and the hyperlink) - but the black and white kind.

I couldnt find documentation in Excels Help, in MS KB or MS elsewhere for "shift+edit"... but did not have time to read all billions of hits and articles that were returned with any wild guess and content about Shift and edit that were not even closeley related - no surprise(?). Do you know where i can find more info about this (undocumented) "shortcut"?

And lastly, what do you mean by "...Active copy...", is there an "inactive" one too?

Thank you,
Stefan

Re: add hyperlink to part of cell text, not entire cell

Hi Barry,
Thank you. I was afraid that this would be the case. I've already considered using a drawing object/text box and will add the hyperlink to it. Should be good enough. - Yet, other suggestions are always welcome.
Stefan

Hello,[SIZE="1"]<try to phrase that title logically!>[/SIZE]

If, say cell A1, contains text such as:
"This Forum is wonderful, to see a great example for a post [COLOR="Blue"]click here[/COLOR]." [SIZE="1"]<This is not an actual link here!>[/SIZE]
Is it possible to have the "click here" part as a hyperlink, and not the entire cell?
Or- do i have to add an "image" or something similar to which i can add the hyperlink and then anchor the "image" to that cell?

Stefan

Re: Read “My Documents” Path And Use Result

Hi,
While i dont fall in that group of "esteemed group of professionals" :-), maybe this msg tread will be of help along the way?
As for the ActiveWorkbook.Path, you could use this

ActiveWorkbook.SaveAs Filename:= _
        ActiveWorkbook.Path & "\New Folder\FileName.xls"

I think that i'd use the direct path without assuming that the ActiveWorkbook is always in the "right" place, mine is like this.

ActiveWorkbook.SaveAs Filename:= _
        "C:\Documents and Settings\UserName\My Documents\New Folder\FileName2.xls"

I suppose you could use the Macro Recorder (Tools| Macro| Record New Macro) along the way as well. - I'm sure you'll get a bunch more/different suggestions too.
Stefan

Re: Hide rows on multiple pages

Hi,

Quote

cycle through 15 named sheets

I figured so much ;-). How about this?

Sub MultiplePageHideRows2()
Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        WS.Activate
            Rows("2:2").EntireRow.Hidden = True
            Range("A1").Select
    Next
    Sheets("Sheet1").Select
End Sub