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!

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!

- Can I use 3-D reference range as argument in formulas like C
- Getting the correct Syntax for Range in Sorting
- Adding commas "," in email addresses for sending BULK Email stored cellwise..!
- Adding or Deleteing Rows in a Range with an Array formula
- Dynamic range defined in VBA for use in formulas
- VBA Syntax for If cell is a Formula
- Syntax for range selection
- Problem with commas in exported text files
- Formula for adding a comma in front of text in a cell
- Concatenate range with comma's?
- Populate Range With IF/ISBLANK Formula With Macro Code
- Defining dynamic range to use in formulas
- The syntax for programmatically inserting a "/" in the middle of a concatenated cell
- Correct VBA syntax for cell function formula
- Importing CSV with commas in fields
- NA for cells with text in!!!
- Sum of last 3 numbers in a range with gaps.
- Indirect Syntax for VBA
- Syntax For Range with 2 Variables
- Sum numbers entered with commas
- How to define a Range with variant?
- Naming a Range with VB
- Can Named ranges be used in file link formulae?
- Looking for range of text in a single cell

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

Eli

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

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

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

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!

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.

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

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.

4000 rows. Can you help me with a fromula or a macro. Thanks and regards

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!

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

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.

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?

=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

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.

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???

Thanks.

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,

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 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.

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

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.

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?

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