Posts by vtcin

Re: VB code for indenting text 3 spaces

Yes Patel, it's an excel macro with output to a word doc... I think it's working the way it needs to now for the final output...........you folks are great with your help to us newbies!

Re: VB code for indenting text 3 spaces

Thank you. I tried this below and when it cycled through the output to a word doc did what I wanted.

.typetext Text:" "+Name

How do I change the code below to create a 3 space indent to the Name within the paragraph?

With .Selection
        '.TypeParagraph
        .Font.Name = "Arial"
        .Font.Size = 12
        .Font.Bold = True
        .ParagraphFormat.Alignment = 0
        .typetext Text:=country
        .typetext Text:=State
        .Font.Size = 7
        .typetext Text:=city
        .Font.Bold = False
        .typetext Text:=Name
        End With

I'm not sure what should be typed above the .typetext Text:=Name

Re: subtraction involving blank cells

Thank you Mumps for the reply and formula. It works to calculate for the blank cells, however, it produces a "false" in the cells that were previously calculated. Is there a way to merge these two formulas?

=IF(OR(B3="",B3=0),IF(B1>B2,B2-B4,""))

=IF(COUNT(B1,B2)=2,IF(B1>B2,B1-B2,""),"")

how do I modify this formula [in A2] to calculate the difference between cell b2 and b4 [result to show in A4 to calculate 0.80] when B3 is blank, empty, zero. Would like formula to go back to the last cell in column B that had a value.

=IF(COUNT(B1,B2)=2,IF(B1>B2,B1-B2,""),"")

A B

Re: Search and Text formula help

Here is a sample worksheet of what I'm trying to do. The cell A5 reads PAID THROUGH LIFE and I'd like it to read Complimentary Life Member.forum.ozgrid.com/index.php?attachment/42599/

Re: Search and Text formula help

Hello folks,
Is it possible to have two ISERR values in this formula? It doesn't seem to work when I keyed in after the first search string for Honorary. The second search would be for Complimentary Life to have it the term "COMPLIMENTARY LIFE MEMBER" result.

I also tried using another if string for the date but the previous two ifs populate and preceed the desired result of Complimentary Life Member.

Would replace or substitute work?
Cin

Merry Christmas EXCEL experts. I am in need of your help once again!

Currently, based on the formula below, the notice field (where this formula resides in column K) will populate with:
EXPIRED mm/dd/yyyy
PAID THROUGH LIFE
HONORARY LIFE MEMBER

These are based on membership types of Regular, Life or Honorary in column T. Column S is the date field. Both Paid through Life and HLM use a date greater than 1/1/2030.

How do I modify the formula below so that an additional membership category called "Complimentary Life" will populate the notice column with "Complimentary Life Membership".

=IF(OR(ISBLANK(T2),ISERR(SEARCH(T2,"Honorary"))),CONCATENATE(IF(S2<TODAY(),"EXPIRED ","PAID THROUGH "),IF(S2>DATE(2030,1,1),"LIFE",TEXT(S2,"mm/dd/yyyy"))),"HONORARY LIFE MEMBER")

Cin

Re: VB and excel to word IF Then False two options

THANK YOU. That did the trick Have a great weekend!

I'm new to macros and the excel VB.

How to I modify the code below to add another renewal type "Honorary Life Member".

How do I add the If Then for this second true statement of <> Honorary Life Member.

Do I use Else If and how?

Here's the section of code as it looks now:

.typetext Text:=renewalnote
If renewal <> "PAID THROUGH LIFE" Then
.typetext Text:="Membership renewals "
.Font.Bold = False
.typetext Text:="are $10 per calendar year per household or $200 for lifetime membership." & vbCrLf & vbCrLf
.typetext Text:=" Number of years: ________ x $10 per year = $_________" & vbCrLf
.typetext Text:=" Lifetime membership is $200 (Does not include yearly Gathering fees.) $_________" & vbCrLf & vbCrLf
End If
.Font.Bold = True

Re: SUMPRODUCT and unique occurances of abbreviation

A HUGE THANK YOU to Rory! Yes, that's it! Til next time............you folks are GREAT.

Re: SUMPRODUCT and unique occurances of abbreviation

Sorry about that, new to this forum again, I do apologize.

I attached a sample workbook.

Re: SUMPRODUCT and unique occurances of abbreviation

I put the asterisk in front of the MT only to show as a "wildcard", because all abbreviations with the letters MT are appearing when I only want the MT not the JMT or BMT, etc. It's acting like the "find" function. Giving me all results with the letters MT.

However, to complicate this, I am looking for MT, JMT, BMT, etc as trail abbreviations. The abbreviations in column A (range Trail) are separated by a comma. I was hoping that I didn't have to convert the commas to new cells, then run an array formula.

I put up a screen shot in an earlier post........#4

Re: SUMPRODUCT and unique occurances of abbreviation

Thank you however, both #2 and #3 return O for results.[ATTACH=CONFIG]36975[/ATTACH]

How do I modify the sumproduct formula to identify individual occurances of letter combinations.

I have a column of trail abbreviations A2:A527 called this column Trail, my range is Trail.

There are trails abbreviated as MT, JMT, BMT, *MT in column A.

=SUMPRODUCT(--ISNUMBER(SEARCH("MT",Trail))) will give me all occurances of MTand includes JMT, BMT because the letters MT are in those last two abbreviations. It found 124 occurances when there are only 6.

How do I modify this formula to find only what I'm looking for?