Free Microsoft Excel 2013 Quick Reference

Syntax for Range with Comma in Formula

Hello,

The following function works fine:
=DSUM(DataTable,"FTE", A30:A31)

However, for the next row I would like to evaluate the following, because I want to skip row 31 (A30,A32 being a range):
=DSUM(DataTable,"FTE", A30,A32)

However, because the formula requires a comma to delimit the formula, a range containing a comma gives an error.

How can I define the range cell A30 and cell A32 (excluding 31) without using a comma, or within a formula?

Any help would be greatly appreciated!


Hi formula experts!

Can I use 3-D reference range as argument in formulas like COUNTIF, SUMIF, SUMPRODCT, PT, LOOKUP etc.?

Eli

Hi all,

I'm trying to get the correct syntax for identifying the range in a variable sized table on sorting. The line using a fixed range reference - Range("E2:E296") - works but doesn't cater for the changing table size. I've tried syntax in the line above to correct this but it's giving me an error. Can anyone advise what the correct syntax is please,

thanks, Neil

Sub a1()
   
    
    With ActiveWorkbook.Worksheets("Analysis Sheet").Sort      
        .SortFields.Clear
        .SortFields.Add Key:=Range("d2", ActiveCell.End(xlDown)).Address,
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal            
        .SortFields.Add Key:=Range("E2:E296"), SortOn:=xlSortOnValues,                         Order:=xlAscending,
DataOption:=xlSortNormal         
        .SetRange DataRange            
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply                                      
    End With
    
  
End Sub


Adding commas "," in email addresses for sending BULK Email stored cellwise..!

Dear Forum,

I have around 200-250 Email addresses in an Excel Sheet and I need to send BULK Email to all these email addresses, however typing the same manually is tedious and time-consuming as its a regular activity every week-end.

Is there a way by FORMULA or VBA whereby I could get all these email addresses in a single cell with commas in between each email address after the first and just before the last email address in the range.

Warm Regards
e4excel

Hi,
Is it possible to add or delete rows in a range containing an array formula
and maintain the array? I have a couple of routines for I use for worksheets
that do not contain array formulas, but I know they won't work with the array
formula. Here is my current add row / delete row code, can it be modified to
handle the array formulas?

Option Explicit

Private Sub AddRowCA_Click()
Dim rngEntryBottomRow As Range
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("geekk")

Set rngEntryBottomRow = Range("Below_Entry_Bottom_RowCA").Offset(-1)
With rngEntryBottomRow 'rngI
.EntireRow.Insert
.Copy Destination:=.EntireRow.Offset(-1)
.Range("A1,C1:D1").ClearContents
End With

ActiveSheet.Protect ("geekk"), DrawingObjects:=True, Contents:=True,
Scenarios:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

Private Sub DeleteRowCA_Click()
Dim Response As Integer
Dim rngEntryBottomRow As Range

Set rngEntryBottomRow = Range("Below_Entry_Bottom_RowCA").Offset(-1)

'if last detail row is blank, delete one detail row and If not empty
' then msg box to explain error and exit sub.
If Application.WorksheetFunction.CountA(rngEntryBottomRow) > 7 Then
MsgBox "You are attempting to Delete a Row that contains User
Input." & _
" Delete Row Failed", vbOKOnly + vbCritical, "Can Not Delete" & _
" Row with Information"
If Response = 0 Or 1 Then Exit Sub
End If

If Application.WorksheetFunction.CountA(rngEntryBottomRow) = 7 Then
Application.EnableEvents = False
Application.ScreenUpdating = False
ActiveSheet.Unprotect ("geekk")
With rngEntryBottomRow
.EntireRow.Delete
End With
End If

ActiveSheet.Protect ("geekk"), DrawingObjects:=True, Contents:=True,
Scenarios:=True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
--
Casey

Hi, I need some help with dynamic ranges and filling in formulas in the
spreadsheet. Basically, I copied and pasted data into column A, which drives
formulas in column B. The formulas in column B extend longer than the data
in column A, so i end up with a bunch of zeros at the end. I used the
following VBA code to define the non-zero range in column B.

Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("B1:B" & LastRow)

In column C of my spreadsheet are various percentiles that i need to find.
here's my question: I want to fill in a formula in column D that uses the
range that i defined in the VBA code and the percentiles stipulated in column
C. How do I code that in VBA? Thanks for any help that you can provide!

Hello
I am trying to right a macro that if a cell in a range has a formula I want it to change the formula.

I know how to change it if the cell in the range is empty, but don't know the syntax if their is already a formula in place.

This is what I use when he cell is empty
Public Sub Reset()
For Each ce In Range("G8:G15")
If IsEmpty(ce) Then
ce.FormulaR1C1 = "=RC[-1]*R10C1"
ce.Font.ColorIndex = 11
End If
Next
End Sub

I think the line i need to change is "If IsEmpty(ce) Then", but not sure what to??

thanks for any suggestions.

Hi folks,
I have a range say from A2:B11 on a sheet
range ("A2:B11").select
with selection I am formating a bunch of things
then I like to select an identical range below that
range ("A12:B21")
What would be the syntax for this increment in range?
Then I am going to repeat the formating steps on this range.
It would do this untill the end of data.
Appreciate your help please.
Thanks
Syed

Hi all - this has been driving me nuts all night as I can't think how to remedy it.

Essentially I am taking a list of file names from one sheet and, with a macro, having it save the contents of various cells in a .txt file (masked as an .sfv file for file checksum checking).

The problem I am having though, is any filenames (values) that have a comma in them, come out in the .sfv file with quotation marks either side of them. This is not ideal - I was hoping somebody would be able to help me!

This is how my macro is looking at the moment:

Sub SFV()

Dim strDate As String
strDate = Format((Date), "yyyy-mm-dd")

    Range("A1184:A1225").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="C:filename_(" & strDate & ").sfv", FileFormat:=xlText,
CreateBackup:=False
    ActiveWindow.Close False
    Range("A1").Select
End Sub

Here is an example of what the contents of the .sfv file comes out like:

file.1.xls 12345678
file_2.avi 87654321
"file,3.doc 12348765"

I hope somebody can help me! Any help would be appreciated. Cheers.

I need to add a comma in front of the text in a cell and copy the same for
4000 rows. Can you help me with a fromula or a macro. Thanks and regards

I have a range which is found by carrying out some custom filters.

Is there a piece of code to concatenate the cells found into one line with commas between them on another sheet?

e.g

In Sheet 1 After the filter Column A has the following data visible

A1 = Title
A5 = 1234
A9 = 4567
A15 = 4325
A300 = 9245

So Sheet 2 Range A1 would be

1234, 4567, 4325, 9245

This needs to be done 10 times to fill Range A1:A10 with data (each search is different).

Any help, as ever, greatly appreciated!

Hi Folks,

Wondering if anyone could help me on this. Seems quite basic but I couldn't find a similar thread on this, so please point me in the right direction if it’s been asked previously.

The sheet has data in column "A" through "J". Number of rows varies.

I want a Macro to enter a formula “=IF(ISBLANK(A2),"",A2)” in column “K”, starting at cell K2, down the entire column within the data range, stopping at the last row that has data in column "A".

Cells in Column would end up looking like this…

(K2) - “=IF(ISBLANK(A2),"",A2)”
(K3) - “=IF(ISBLANK(A3),"",A3)”
(K4) - “=IF(ISBLANK(A4),"",A4)”
(K5) - “=IF(ISBLANK(A5),"",A5)”

…. To the last row with data in column “A”
- any ideas?

Thanks in advance,

Jim

My question is: After I define a range in VBA code, how can i use it in formulas such as the percentile formula? I did a search on the forums to find this code:
Dim LastRow As Long
Dim rng As Range
LastRow = Range("A65536").End(xlUp).Row
Set rng = Range("B1:B" & LastRow)

Basically, I paste data in column A, which drive a formula in column B. Column B fills in based on data in column A and fills in 0's for the rest. The above code allowed me to select the non-zero range. Now i want to find the percentile of that range in cell C1. HOw do I do that?
Thank you for any help you can give me.

Hello--Does anyone know the syntax for programatically inserting a "/"
between the two added numbers of a concatenated cell? I want the value
of, say, Range("a1") to equal the value of range ("a2") "/" range
("a3"). What is the syntax for telling excel to insert the slash?
A stripped down version of my code looks like this :

Sub Concat()
Range("a1").Formula = "=Value(A2&A3)"
End Sub

I want the "/" between A2 and A3.

Does anyone know the syntax?

The following formula works great in a worksheet cell at counting every 5th row (5 rows total) that contains data:

=SUMPRODUCT(--(A1:A100<>""),--(MOD(ROW(A1:A100),5)=1))

However, I can't get the correct syntax for it to work in VBA code.
Can someone help? Thanks a million. mikeburg

I'm trying to import a CSV file that has commas in some of the fields i.e. "England, UK" is one field but what Excel is trying to do when it imports that file is to split those two words up as it's seeing the comma - even though I have quote marks around the term.

So for example my CSV is formatted like this:

John,Doe,"England, UK"

Which should come into 3 cells like so:

John | Doe | England, UK

But it's actually ignoring the quote marks and importing like this, i.e. 4 cells with the quote marks

John | Doe | "England | UK"

Not sure what to do so any help would be greatly appreciated.

Hi,

I have a spread sheet that works of formulas which displays text, the only problem is that when there is no information it puts NA in the cell, i have put the formula for the cells containing sum equasions

=if(isna(your formula),"",your formula) but it doen't work for cells with Vlookups & time should it work???

I have a range where some cells contain numbers and other (random) cells are empty. Can I calculate the sum of the last 3 numbers in that range with a single formula? What would such a formula have to look like? (Preferably, the formula will continue to work if I later expand the range and add new numbers and new gaps at the end.)

Thanks.

Hello,

I have been looking for the syntax for the indirect function in VBA but I have read on several message boards that you can not use it directly in vba?

Range("F2").FormulaR1C1 = "=indirect(" '"+range("e2")+"'!"+range("d2")"

This however, does not work.. Is there any way around this. I have 10 sheets where I want the same information from the same cell.

Thanks,

I have two variables:
UpperLeft which evaluates to upper left cell of desired range (J41)
LowerRight which represent lower right cell of desired range (L71)

How do I combine them in a Range().Select statement?

I received a spreadsheet where they input the numbers with commas in every cell as opposed to formatting the cells.

I think theres a way in excel that would allow me to change the format so I can sum the cells or formulas on the cell.

I can't remember how to do it.

Hi all,

I would like to define a range with variant in VBA program. For example,
inside a for-loop, when i=1, the selected range is Range("A1":"F1"); when
i=2, the selected range will be Range("A2":F2") and so on. But I don't know
how to define such "Range". Can anyone help me? Thanks a lot!!

C.F. Yiu

I have a loop that I run through where the name of the
CurrentRange variable changes each time through.

For example, if the CurrentRange variable is "Justin's
Range" and the CurrentSheet variable is "Justin's Sheet",

I want to name the range, "Justin's Range", with the
following formula:

"=OFFSET("Justin's Sheet"!$A$1,0,0,
COUNTA("Justin's Sheet"!$A$1:$A$365),1)"

This is the code I'm using:

ActiveWorkbook.Names.Add Name:=CurrentRange,
RefersToR1C1:= _
"=OFFSET(CurrentSheet!$A$1,0,0,
COUNTA(CurrentSheet!$A$1:$A$365),1)"

I think it's trying to find a sheet called "CurrentSheet"
instead of computing the formula out.

Any help would be appreciated.

Hi.

I have an expression in my main Management accounts workbook.

=GESTEP(CurrentPeriodNumber,AE$451)*
ROUND(SUMIF(FigaroTBMaster.xls!FigeroYTDNominal,"= "&$A34,
FigaroTBMaster.xls!FigeroYTD04),0)

Were "CurrentPeriodNumber" =4,
AE$451 =4,
$A34 ='99999
"FigaroTBMaster.xls" is the workbook containing the source data,
"FigeroYTDNominal" and "FigeroYTD04" are named ranges within the source file.

The expression works when the source file is open, but not when it is closed.

Can named ranges be used in formulae which link workbooks?

I just started a new job and my company already had a file with about
16,000 records in it (called Products). This file contains all of the
parts numbers that they carry. In this file there is one column called
"description". This cell contains the dimensions, color name and some
other information such as if the piece is flat or rounded. I have
another file that has about 400 records and this file tells me "color
name" as well as the "type" of stone that it is (called Stone_Type).

What I am trying to do is add a field to the Products file that will
say stone type. Is there a way to have Excel search the "description"
from Products for "color name" from Stone_Type and if it finds it
return "type"? As previously mentioned "description" contains things
other than just the color name. To further complicate things there is
no consistency as to where in the cell the color name is (meaning I
cannot extract just that data). Most of the color names are more than
one work long.

If the "description" field matched "color name" I could just do a
vlookup and return "type" but with there being other data in the field
I don't know what to do. I thought about putting each work in its own
column and doing a whole bunch of vlookups but the problem with that is
that most of the names are more than one word long. What I am thinking
about now is using the find function to look for the "color name" in
"description" and if it finds it then return the cell reference so that
I can run a vlookup off of that. I think this will work but I don't
know how to make it search all of the "color name’s (A2:A400 in
Stone_Type).

Does anyone know how I could do this or know of a better way to go
about it? As stated above I have 16,000 record I have to do this to so
I am sure this will take a very long time to run but once it is done the
first time that is it and I can hard code the values so I do not really
care how long it takes.

Thank you. I am not sure if I did a decent job explaining this if
there are any questions please ask and I will try to respond a.s.a.p.

--
hatter
------------------------------------------------------------------------
hatter's Profile: http://www.excelforum.com/member.php...o&userid=26762
View this thread: http://www.excelforum.com/showthread...hreadid=400154