Free Microsoft Excel 2013 Quick Reference

Specifying which range a value is in Results

i have four combo boxes on one sheet. on 2nd sheet i have a lot of data which have particular column headings (date on rows). what is the best way to search for the comboxbox values headings and dates in sheet 2 and then specify those columns as the range to chart.

many thanks

I am using a macro in an Excel template which allows you to optionally
replicate a list. A page break and the new list will insert below the
original. I am trying to do this in the macro by copying the rows which
include the full list and then pasting in rows below. That's fine if I'm
copying the entire list, but when data is entered in the first list, the
"insert copied cells" function no longer pastes it as a list, but only the
formats and values. I thought I could just re-select the range and create a
new list, but I don't know how to make that range relative to the row
location of the new headers, which depends on the length of the first list.
Does this make sense? Is there a vastly easier way to do this? Basically, I
have a list, and I want the option to create a separate list on the same
sheet which will print on a new page.

In my code, I'm trying to specify a defined Name range. The specific
defined Name to be used is determined by the contents of C29.
I've tried the following which doesn't work.

res = Application.Match(cell.Value, Range(Range("C29").Value), 0)

If I use that exact range of the defined Name (as seen below), it works as
expected.
res = Application.Match(cell.Value, Range("L2:L50").Value, 0)

Does anyone know how I get get this statement to work accuratly when
referring to a defined Name range?

Thanks,
Paul

Hello,

I have an Excel 2003 spreadshet with 50+ tabs where in each tab I need to
copy formulas down a certain number of rows across a large number of columns
Each tab is different in terms of how many rows need to be copied. And, the
number of rows for each tab changes periodically. I currently do this
manually but I sometimes don't get everything copied properly on the first
shot so I thought I would be better off if I had a macro automate the process.

Cell A1 contains a value which is equal to the last row number that the
copying needs to go down to. Row 5 is the first row containing the formulas
that need to be copied. Thus, for columns A:CZ from row 5 to the row
specified in cell A1, I need to "copy down" those formulas

A static address/range macro with the last row of 158 (as specified in cell
A1) would look like:
*********************
Range("A5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A5:CZ158").Select
Selection.FillDown
*********************
I think I need to replace the "158" part of <Range("A5:CZ158").Select> with
something dynamic, that changes with the value in cell A1.

I tried:
*********************
Dim Number_of_Rows As Integer
Number_of_Rows = Range("A1").Value

Last_Cell_for_FillDown = "CZ" & A1
Range_For_FillDown = "A5:" & Last_Cell_for_FillDown

Range(Range_For_FillDown).Select
Selection.FillDown
*********************
Frankly, I don't know if I need the DIM or not. The macro stopped at the
RANGE line with a <Run-time error '1004'. Method 'Range' of object'_Global'
failed>. When I hit the HELP button, a Visual Basic Help window popped up
wtih nothing in it.

Any help will be greatly appreciated.

Hello, Again,

I am using

D = .Range("G2").Value
E = .Range("H2").Value

to give me two cell references (as values from the cells G2 and H2) which i
then want to use to form a range by something like

..Range(A:B).value
but this doesn't work so is obviously wrong, can anyone help,

thanks

Andy

I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:

F|45|A|30|F|15|F|10

Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.

SUMIF(A1:G1,"F",B1:H1)

What I need to do is specify a range of every second cell in the row
(starting with Cell A1) for validating they equal F, and a range of the
alternating cells (starting in column B1) for the range containing the data
to add. How can I specify these ranges (I can't name each cell individually
as I have more than 30 cells to add up in my real life situation and the IF
function allows selection of no more than 30 values)?

Hello--

I am encountering difficulty working with arrays and ranges in worksheet
functions. Are arrays interchangeable with ranges in functions such as
Large, Index, and Match? If so, how would I specify a particular row in a
multi-dimensional array?

Example:

function minscores( byref myscores as range ) as range ' return new
values to the range when done
dim scores as variant
dim lowval as integer
scores=myscores ' creates 2xn scores array
lowval=worksheetfunction.large(scores, 1) ' get largest score
This returns a variety of errors depending on how I reference scores in the
function.
How can I refer to the correct row of scores in the line above?

Related question: If I can't use arrays in this fashion, then I have to work
with the range directly. Can I create a "virtual range" to work with so
that I don't have to find a work area in my spreadsheet to do calculations
in?

Many thanks for any insight you can provide.

Bonus question:
What I'm trying to do is highlight the three lowest scores (which might be
duplicated) in a 5-cell range.
I've been copying the range to an array and trying to use the above
functions, which gets very convoluted (the functions return the value of the
lowest number, not the index of the cell of the lowest number), and
ultimately doesn't work because of the array/range problem mentioned above.

dim lowscore(5) integer ' index of lowest scores
dim scores(1,5) as integer ' the input scores
dim i as integer
for i =1 to 5
scores(0,i)=(scores(1,i)+.01*i)*100 ' make each score different by adding
..01, .02, then multiply by 100 to get an integer
next i
for i=1 to 3
tmpval=worksheetfunction.large(lowscores( ),6-i) <---problem with
lowscores() in this line
lowscores(i)=worksheetfunction.match(tmpval, lowscores(), 0) <----problem
with lowscores() in this line
next i

Hi and thanks in advance,

I found some code to compare the values in two spreadsheets by dumping everything into two arrays and outputing any differences.

I have a form where the user can specify the range to compare, by selecting a start column and start row and and end column and end row.

Code works fine when assigning more than one cell to the arrays eg. (strRangeToCheck = "A1:Z5000") but not if assigning a single cell eg. (strRangeToCheck = "A1:A1") OR (strRangeToCheck = "A1"). Crashes on line 4 of code below with Type Mismatch error and when I debug it says the two arrays are empty ...

strRangeToCheck = cboStartCol.Column(1) & txtStartRow &
":" & cboEndCol.Column(1) & txtEndRow
varSheetA = Worksheets("Dataset 1").Range(strRangeToCheck)
varSheetB = Worksheets("Dataset 2").Range(strRangeToCheck)
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)        
     For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
Also, I occasionally get an Out of Memory error when assigning values to the arrays which is most likely due to them being variant arrays ... any suggestions on how to avoid/minimize this error?

Thanks
Deutz

Hi guys.

I have two cells i wish to use as a date range - E9 [from date], E10 [to date].
using the values put into these two cells. delete all rows that the value in column A (which is a list of dates, & on a different worksheet titled "Database") is outside of the specified date range.

Regards

Dear all

I am running the below code which searches for combinations of values that add up to a given target. Depending on the number of values it is searching through, the time it takes obviously goes up.

Option
Explicit

Function RealEqual(a, b, Optional Epsilon As Double = 0.00000001)
    RealEqual = Abs(a - b) <= Epsilon
    End Function
Function ExtendRslt(CurrRslt, NewVal, Separator)
    If CurrRslt = "" Then ExtendRslt = NewVal _
    Else ExtendRslt = CurrRslt & Separator & NewVal
    End Function
Sub recursiveMatch(ByVal MaxSoln As Integer, ByVal TargetVal, InArr(), _
        ByVal HaveRandomNegatives As Boolean, _
        ByVal CurrIdx As Integer, _
        ByVal CurrTotal, ByVal Epsilon As Double, _
        ByRef Rslt(), ByVal CurrRslt As String, ByVal Separator As String)
    
     Dim i As Integer
        For i = CurrIdx To UBound(InArr)
        If RealEqual(CurrTotal + InArr(i), TargetVal, Epsilon) Then
            Rslt(UBound(Rslt)) = (CurrTotal + InArr(i)) _
                & Separator & Format(Now(), "hh:mm:ss") _
                & Separator & ExtendRslt(CurrRslt, i, Separator)
            If MaxSoln = 0 Then
                If UBound(Rslt) Mod 100 = 0 Then Debug.Print "Rslt(" & UBound(Rslt) & ")=" &
Rslt(UBound(Rslt))
            Else
                If UBound(Rslt) >= MaxSoln Then Exit Sub
                End If
            ReDim Preserve Rslt(UBound(Rslt) + 1)
        ElseIf IIf(HaveRandomNegatives, False, CurrTotal + InArr(i) > TargetVal + Epsilon) Then
        ElseIf CurrIdx < UBound(InArr) Then
            recursiveMatch MaxSoln, TargetVal, InArr(), HaveRandomNegatives, _
                i + 1, _
                CurrTotal + InArr(i), Epsilon, Rslt(), _
                ExtendRslt(CurrRslt, i, Separator), _
                Separator
            If MaxSoln <> 0 Then If UBound(Rslt) >= MaxSoln Then Exit Sub
        Else
            'we've run out of possible elements and we _
             still don't have a match
             
            End If
   
        Next i
        
    End Sub
Function ArrLen(Arr()) As Integer
    On Error Resume Next
    ArrLen = UBound(Arr) - LBound(Arr) + 1
    End Function
Function checkRandomNegatives(Arr) As Boolean
    Dim i As Long
    i = LBound(Arr)
    Do While Arr(i) < 0 And i < UBound(Arr): i = i + 1: Loop
    If i = UBound(Arr) Then Exit Function
    Do While Arr(i) >= 0 And i < UBound(Arr): i = i + 1: Loop
    checkRandomNegatives = Arr(i) < 0
    End Function
Sub startSearch()
    'The selection should be a single contiguous range in a single column. _
     The first cell indicates the number of solutions wanted.  Specify zero for all. _
     The 2nd cell is the target value. _
     The rest of the cells are the values available for matching. _
     The output is in the column adjacent to the one containing the input data.
     
    'Range("B352").Select
    'Range(Selection, Selection.End(xlUp)).Select
    
    If Not TypeOf Selection Is Range Then GoTo ErrXIT
    If Selection.Areas.Count > 1 Or Selection.Columns.Count > 1 Then GoTo ErrXIT
    If Selection.Rows.Count < 3 Then GoTo ErrXIT
    
    
    
    Dim TargetVal, Rslt(), InArr(), StartTime As Date, MaxSoln As Integer, _
        HaveRandomNegatives As Boolean
    StartTime = Now()
    MaxSoln = Selection.Cells(1).Value
    TargetVal = Selection.Cells(2).Value
    InArr = Application.WorksheetFunction.Transpose( _
        Selection.Offset(2, 0).Resize(Selection.Rows.Count - 2).Value)
    HaveRandomNegatives = checkRandomNegatives(InArr)
    If Not HaveRandomNegatives Then
    ElseIf MsgBox("At least 1 negative number is present between positive numbers" _
                & vbNewLine _
            & "It may take a lot longer to search for matches." & vbNewLine _
            & "OK to continue else Cancel", vbOKCancel) = vbCancel Then
        Exit Sub
        End If
    ReDim Rslt(0)
    recursiveMatch MaxSoln, TargetVal, InArr, HaveRandomNegatives, _
        LBound(InArr), 0, 0.00000001, _
        Rslt, "", ", "
    Rslt(UBound(Rslt)) = Format(Now, "hh:mm:ss")
    ReDim Preserve Rslt(UBound(Rslt) + 1)
    Rslt(UBound(Rslt)) = Format(StartTime, "hh:mm:ss")
    Selection.Offset(0, 1).Resize(ArrLen(Rslt), 1).Value = _
        Application.WorksheetFunction.Transpose(Rslt)
    Exit Sub
ErrXIT:
    MsgBox "Please select cells in a single column before using this macro" & vbNewLine _
        & "The selection should be a single contiguous range in a single column." & vbNewLine _
        & "The first cell indicates the number of solutions wanted.  Specify zero for all." & vbNewLine _
        & "The 2nd cell is the target value." & vbNewLine _
        & "The rest of the cells are the values available for matching." & vbNewLine _
        & "The output is in the column adjacent to the one containing the input data."
    End Sub
When the code is searching through about 30 or more values excel comes up with the message (not responding).

I am not sure whether the code has crash or is still in fact running.

To test this I wish to place a temporary message box to appear, perhaps using the below code. I wish the message box to pop up once every minute for a few seconds to prove the code is still running.

However I can not work out where to place it in the original code.

Can anyone help?

Many thanks in advance,

Jim

I am trying to unlock cells in a worksheet by comparing the current username to the username(s) of the person(s) who entered the data in that column of the worksheet and not allow them to enter in that cell unless it does not match. There is a workbook with a worksheet “Audit Trail”, which contains in in the same rows of columns A, B and D, respectively, the cell address, the worksheet name and the username of who entered the data in the cell specified in columns A & B of that row. I add the worksheets that contain the cell that I want to unlock to this workbook, then save as a new workbook. The code would need to find the worksheet name that matches the cell to be unlocked in the “Audit Trail” worksheet, then select the cell addresses that have the same column as the cell to be unlocked and then finally comparing the username(s) to the current user and if there are no matches, unlock the cell. There are 2 additional problems with the way it is currently set up: 1st- some of the worksheets that are added to the workbook have multiple pages that are one below another – so I will probably have to change these so that the pages are side-by-side instead so that each column is on only one page; 2nd – in the worksheets that are added to the workbook the cells that need to be unlocked might be in different locations on the worksheet, so maybe if I set up a range with a consistent name for the cells that I want unlocked would work. If anyone has any ideas how to do any of this please let me know. I have attached a workbook with an Audit Trail worksheet and 2 lot worksheets below along with the current code that is use in this workbook.

If you want to know the background I had 3 threads running previously, which intertwined and led to this thread. If interested you can look at those threads, but I’m not checking them anymore for new posts. They are: “MessageBox error and adding it to a Beforesave to an audit trail” , “Lock cells in worksheet after they are changed” and "Beforesave selecting all worksheets except one". These and posts and in other forums have brought me to my current workbook, which has a workbook code and a Userform1 code for a ComboBox. This workbook records all changes in the worksheets except the “Audit Trail” worksheet in the “Audit Trail” worksheet in columns A through H. These columns list the following information for any cell that is changed in the other worksheets in the workbook in order from A - H: Cell Address, Worksheet Name, Date and Time of the Change, the User Name of the User that Made the Change, the Previous Value of the Cell, the New Value of the Cell, the Reason for the Change and if the Cell is Locked. The workbook code is as follows:
Dim PreviousValue As Variant

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  PreviousValue = Target.Value
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'Sh refers to the worksheet that the change took place on
'Target works just like it does in Worksheet_Change() event processing
'it is just specific to the Sh sheet that the change took place on
'
  Const auditSheetName = "Audit Trail"
  Const nonAuditWSPassword = "xyz"
  Const auditWSPassword = "xyz"
  Dim NR As Long
  
  'if the change took place on the Audit Trail worksheet, ignore!!
  If Sh.Name = auditSheetName Then
    Exit Sub
  End If

  If Intersect(Target, Sh.Range("A1:DW400")) Is Nothing Then Exit Sub
  
  With Application
    .EnableEvents = False
    .ScreenUpdating = False
  End With

  With Sheets(auditSheetName)
    .Unprotect Password:=auditWSPassword
    NR = .Range("A" & Rows.Count).End(xlUp).Row + 1
    .Range("A" & NR).Value = Target.Address(False, False)
    .Range("B" & NR).Value = Sh.Name
    .Range("C" & NR).Value = Now
    .Range("D" & NR).Value = Environ("username")
    .Range("E" & NR).Value = PreviousValue
    .Range("F" & NR).Value = Target.Value
    .Protect Password:=auditWSPassword
  End With

  If PreviousValue = "" Then
    With Sheets(auditSheetName)
      .Unprotect Password:=auditWSPassword
      .Range("G" & NR).Value = "New Data"
      .Protect Password:=auditWSPassword
    End With
  Else
    'get reason for change from user via UserForm
    UserForm1.Show
  End If
  Application.EnableEvents = True

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim auditWS As Worksheet
Dim CellListRange As Range
Dim anyCellListed As Range
Const auditSheetName = "Audit Trail"
Const firstAuditEntryRow = 2
Const dateCol = "C"
Const lockedCol = "H"
Const cellCol = "A"
Const wsNameCol = "B"

Set auditWS = ThisWorkbook.Worksheets(auditSheetName)
Set CellListRange = auditWS.Range(cellCol & firstAuditEntryRow & _
 ":" & auditWS.Range(cellCol & Rows.Count).End(xlUp).Address)

Application.ScreenUpdating = False
For Each anyCellListed In CellListRange
  If IsEmpty(auditWS.Range(lockedCol & anyCellListed.Row)) Then
    'need to lock the cell on the specified worksheet
    With Worksheets(auditWS.Range(wsNameCol & anyCellListed.Row).Value)
      .Unprotect Password:="abc"
      .Range(anyCellListed.Value).Locked = True
      .Protect Password:="abc"
    End With
    'record the locked status on the Audit Trail sheet
    With auditWS
      .Unprotect Password:="xyz"
      .Range(lockedCol & anyCellListed.Row) = "Locked"
      .Protect Password:="xyz"
    End With
  End If
Next
'housekeeping - release objects back to the system for reuse
Set CellListRange = Nothing
Set anyCellListed = Nothing
Set auditWS = Nothing
End Sub
The userform1 code is as follows:
Private Sub CommandButton1_Click()
Dim LastRowA As Long
Dim LastRowG As Long
Dim Reason As String

Reason = Me.ComboBox1.Value
If Reason = "" Then
    Me.Label2 = "You MUST select a reason!"
    Exit Sub
End If
With Sheets("Audit Trail")
    LastRowA = .Range("A" & Rows.Count).End(xlUp).Row
    LastRowG = .Range("G" & Rows.Count).End(xlUp).Row
    .Unprotect Password:="xyz"
    .Range("G" & LastRowG + 1 & ":G" & LastRowA) = Reason
    .Protect Password:="xyz"
End With
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim Reasons As String
Dim ReasonArr
 
Reasons = "New Data,Typographical Error,Incorrect Anaytical Result Form entered,Incorrect Result in Analytical Result
Form" '<=== Change reasons here
ReasonArr = Split(Reasons, ",")
For r = 0 To UBound(ReasonArr)
    Me.ComboBox1.AddItem ReasonArr(r)
Next
Me.ComboBox1.Value = ReasonArr(0) 'Reason1 will be default
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Prevent user from closing with the Close box in the title bar.
    If CloseMode <> 1 Then Cancel = 1
End Sub
the workbook is attached below. Please help if you can. Thank you in advance.

Hello!

Background: (read or just go to the actual question below ) I have a macro that will add a new row below another row, and fill the first cell of that row with a value that equals the value of the cell above + 1. Example:
Payment No. 1 in the amount of ... on ...
Payment No. 2 in the amount of ... on ...
I do this successfully via command button calling following macro
 'HiNum = WorksheetFunction.CountIf(ActiveSheet.Cells, "Payment " & "*")
    g = HiNum + 1
With Range("Payments")
    .Offset(g, 0).Insert Shift:=xlDown
    .Offset(g, 0).Name = "Payment" & g
    .Offset(g, 0).Value = "Payment No. " & g
    .Offset(g, 1).Insert Shift:=xlDown
    .Offset(g, 1).Name = "PaymentAmount" & g
    .Offset(g, 1).Value = "in the amount of"
    .Offset(g, 3).Insert Shift:=xlDown
    .Offset(g, 3).Name = "on" & g
    .Offset(g, 3).Value = "on"
    .Offset(g, 4).Insert Shift:=xlDown
    .Offset(g, 4).Name = "Date" & g
    .Offset(g, 4).Value = ""
    End With
Unfortunately, the same worksheet needs to have another payment schedule, where new - consecutively numbered payments - shall be inserted exactly as above. So the CountIf function is now useless because the word "Payment No." will appear on different locations, namely in different payment schedules. The Worksheet is very dynamic (rows will be inserted/deleted depending on various user-choices), so I cannot further specify the range in which CountIf shall count.

Question: Can I use the name of the NAMED RANGE as the argument in the CountIf function?
Like this
Because while the readable text shall always be "Payment No. " I can of course name the range differently depending
on which payment schedule the payment is in.

Thanks in advance for looking into this!

Hi All

I've got a list of users within a sheet. The sheet is split vertically in two. So the users are in column L with password, nickname etc through to column P. They start from row 6. So the first user is in cell L6.

A combo box (selectuser) shows all the users from cell L6 to the last user on sheet "Setup". This works fine.

I've got my combo box linked to other text fields so when you select a user, it displays their nickname in the nickname field etc.

I have a delete button on my userform, which, when clicked, deletes the user.

If I use the following formula, it clears the cells from the row selected based on the selection in the combo box. However when you then load up the userform again, it loads all users fine on selecting from the combo box until you click on a user that was below the one you just removed. It also leaves those cells blank rather than just deleting them.

Sheets("Setup").Range("L" & selectuser.ListIndex + 6).ClearContents
Sheets("Setup").Range("M" & selectuser.ListIndex + 6).ClearContents
Sheets("Setup").Range("N" & selectuser.ListIndex + 6).ClearContents
Sheets("Setup").Range("O" & selectuser.ListIndex + 6).ClearContents
Sheets("Setup").Range("P" & selectuser.ListIndex + 6).ClearContents
So I need to work out how to delete the cells and shift the ones below up a row, based on the combo box selection.

I tried this:

Private Sub deletebutton_Click()

If Not selectuser.Value = "" Then

Msg = "Are you sure you wish to delete user, " & selectuser.Value & " from HBPOS? This action is
irreversable."
        Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
        Select Case Ans
            Case vbYes

Dim RowToDel As String

RowToDel = selectuser.ListIndex + 6

Range("L" & RowToDel, "M" & RowToDel, "N" & RowToDel, "O" & RowToDel,
"P" & RowToDel).Select
Selection.Delete Shift:=xlUp

MsgBox "The user, " & selectuser.Value & " removed from HBPOS."

Unload Admin_UserMenu
Admin_UserMenu.Show

        Case vbNo
        
        MsgBox "Deletion cancelled"
        
        Case vbCancel
        
        MsgBox "Deletion cancelled"
        
End Select

Else

MsgBox "Error: You must select a user to remove"

End If

End Sub
However I get an error saying that there are too many arguments for the range. So I need to figure out how to select cells L,M,N,O,P for the selected row based on the combo box, delete them, and shift the cells below up.

Does anyone know how I can do this?

Thanks

Hi guys

I'm trying to write a short piece of code to prevent me from having to open 200+ .txt files and copy& paste the data into excel!
Basically I have a workbook with 15 sheets in. Each sheet is laid out exactly the same.
In each sheet I need to copy and paste data from .txt files into it in seperate columns The .txt files are in 10 different folders (the .txt in thses folders are all named the same, They are outputs from various model runs).

What I require is a macro that for each worksheet in my workbook, will open a .txt file from a folder, the location of which I can insert in a cell. Copy the data from a column and then paste it into the column that I specify in worksheet.

What I envision is:

1) Manually insert into a master cell, on a master sheet, the column number relating to the column I need the data to be inserted into in each of the worksheets (Cola)

2) Manually Insert the Folder address where the text files are located into a master cell (Fileloc)

3) Command button on the master sheet that when pressed.....

4) the macro will go to the first worksheet get the .txt file name from the cell (4,Cola)

5) open the .txt file based on the .txt filename and location (Fileloc)

6) copy the data from column 2 of teh .txt file and paste into the worksheet cell (6, Cola)

7) then repeat the process for each worksheet, except for the mater sheet which has the cells that I have put the FileLoc and Cola numbers into.

I can then manually adjust the file address and column number and just keep running the macro until all of my columns are filled. Da Dah!!!!

I hope this makes sense and I'd really appreciate any help. I've had a go at putting some code together from other bits and pieces but it doesn't work. I'll try and attach an example of the workbook and code.

p.s. it wont let me attach workbook at the moment but I'll try again later. Not sure the code alone will be much use.

Private Sub
CommandButton1_Click()

    Dim ws As Worksheet, cola As Integer, Spath, filenam, fileloc, txtfile As String

'For each workshhet loop

    For Each ws In Workbooks("SEWCUS Plot Data Example.xls").Worksheets
         
         
cola = Workbooks("SEWCUS Plot Data Example.xls").Worksheets("Sheet1").Range("Column").Value
Spath = Workbooks("SEWCUS Plot Data Example.xls").Worksheets("Sheet1").Range("Location").Value
txtfile = Workbooks("SEWCUS Plot Data Example.xls").ActiveSheet.Cells(4, cola).Value
fileloc = Spath & "" & txtfile
filenam = ActiveWorkbook.Name
tabnam = ActiveSheet.Name

    'Open Text file

    Workbooks.OpenText Filename:=fileloc, Origin:=1256, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=True, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 4), _
    Array(2, 1)), TrailingMinusNumbers:=True

    'Copy .txt file data

    Workbooks(txtfile).Activate
    ActiveSheet.Range("B16").Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

    'Paste .txt file data

    Workbooks("SEWCUS Plot Data.xls").Activate
    ActiveSheet.Range.Cells(6, cola).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    
    Workbooks(txtfile).Close

         
    Next ws
    
    
End Sub

Many thanks again

And merry christmas

Hi, i am trying to write something which will scan through a number of
worksheets which contain matrices. So I am storing the values of each matrix
in an array. I now need to find a way to rank the values in each column
relative to that column of data. My question really is, is there a way to
specify that particular column as a range and then rank the values by
referencing a value in the array against the defined range? So for example
the first value in the first column - array(0,0) against a range defined as
rngColumn1, then array(1,0) against the same range...and so on.

Yesterday I posted the snippet of code that is listed

below. Surely there must be a seasoned programmer out there that has

an idea of how to tackle this, even if the below code is totally

changed. This is what I want to do using VBA code:

I have 5 columns, filled with data on my Excel worksheet.
I have a form made up. To fill a textbox on my form I want to be able

to take data from the worksheet with the following criteria:

If column (c) value = "admission" AND column (d) value = "Home" and

a checkbox on my form is checked:

Then the count of the rows that have column (c) with the specified

value AND column (D) with the specified value, will be totalled and

placed in the textbox on the form.

Here is a snippet of code I had done which will count and put the

total in the textbox, but I cannot figure out how to add in the other

criteria of the column (c) value = "admission". The way it is now, it

is totalling all rows in column (D) with a value of "Home". I cannot

figure out how to have multiple criteria being met.

Dim Ct As Integer
Ct = 0

If ckHome2 = True Then
Range("d2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = "Home" Then
Ct = Ct + 1
txtHome2.Text = Ct
End If

I have been working on this for weeks to no avail. Can someone

please, please, please, please help me with this. Thanks.

Hi,

ok i have an attendance database, one page has dates in the first column in an ascending order and the to row has names and then that goes from left to right, organized by teams. Filling in individual values per day under each individual name who is ill in and on holiday and so on.

I have built a reporting page to report on per team who is in ill and on holiday but by day, so the user will put in the date and then the reporting values will change to that day, the only thing is, is i don't know how to do that so thats why i need someones expertise!

i have code for my main page where the user enters the date they wish to go to and then the program takes them straight to that date:

Sub Macro_Main()
'
' Macro_Main Macro
' Macro recorded 13/09/2005 by Aly McMorland
'

Dim d As Date, yr As String, i As Integer
d = InputBox("Please enter a date e.g. 16/09/2005, the format of the date must be accurate")
yr = Year(d)
i = d - DateValue("12/31/" & yr - 1)
If yr = 2005 Then
Sheets(yr).Activate
Range("A" & 5 + i - 212).Select
Else: Sheets(yr).Activate
Range("A" & 5 + i).Select
End If

End Sub

The reason why it has a If yr = 2005 then is because in the 2005 tab sheet it is only from August to the end of the year, where as the other years have full sheets from january to december.

So what i need is a modification of the above code so that it will change the values in my reporting table, basicaly COUNTIF statements but to change those to the day that the user will specify, so there could be = COUNTIF ('2005'!D6:F6,"IN") but the user changes the day so the countif statement would change to = COUNTIF ('2005'!D10:F10,"IN")

or

if that is too hard as i think it might, i could create a table with all the values in that it could possibly be, the statement would now refference that new table and depending on what the user puts in depends on the values shown from this table.

But if anyone could help that would be great!

thanks!

All -

I have a macro that creates a spreadsheet and saves if off as a tabbed
text file, and then autofilters it. The intent is to save the filtered
data off as a second, smaller tabbed text file. I need both.

So far my attempts have given me the same file contents with a different
name. Two ways forward occur to me, both of which I can't quite see how
best to do. And of course, I suspect strongly that there is a
third/fourth etc simple way to go that I don't at the moment see.

Way 1. Set a range equal to the what would be the autofiltered column
and, For Each cell, delete the entire row that doen't meet the criteria
that would have been specifed for that cell. My problem is that when
these occur in succession, I get every other one. It looks like the
cell's value will not be rechecked when a new cell "slides underneath."
The For Each loop appears to have satisfied itself with that cell and
does not recheck. How does one best deal with that? My fix would be to
cycle thru, count occurences, and wrap a "For Each/If bad Delete
Row/Exit For loop" in a larger loop done "count" times. Seems inelegant
though very do-able.

Way 2. Done manually, I select the columns, open a new spreadsheet, and
paste special values, and save off. I know how to do that
programatically save how to specify the ranges to copy.

Way 3. I don't know about yet.

Thoughts please.

....best, Hash

I'm not entirely sure if this is possible, or even how it should/would work. Ill leave that up to you excel geniuses... Hopefully this makes sense.

As it stands now im using a series of formulas like
 in cells (D,E,F) on (Page 1) to pull info from a range of cells (B,C,D) on (Page 2)

When I enter a preset Device name into "C27" it pulls the corresponding cells info from the last page into that cell. (A total of 11 possible rows/matches)

My goal is to do this same thing, But using a formula to copy a Formula that will test the previous cell in the row.

So.... I enter a value into C27, Cells D27,E27,F27 all test their formula to the range specified. Cell G27 is empty but I will enter a number like 2.5...

On (Page 2) Cell E3 I want to have a formula like
 That will be copied to cell H27 (Page 1) to test the number (G27) to the formula and return a pass or fail.

The problem is until I enter a value into C27 to specify which range from (Page 2) I dont know which formula (E3 thru E14) is going to be copied into H27.
and unless I specify in my formula in Cell H27 that it needs to check cell G27 it doesnt work.

Is it possible to use a formula in one cell (H27), to copy a formula from another cell (E3 Page 2) that would test the previous cell (G27)... when creating the formula you dont know which cell it will be testing until it is copied?

If none of this makes any sense as im afraid it will not, I have attached a workbook with the 2 pages and formulas in question and a brief description on the worksheet as to what im trying to achieve. If that still doesn't make sense a link to a tutorial that covers something along the lines of what im trying to achieve would be awesome!

Thanks in advance for any help anyone can give

~Zees

Hi all

I'm not great with excel so please bear with me.

I need to produce a chart which includes values predominately in the range of 0-50 but some of the values can go up to 600.

I want to produce a chart that can show the detail of the data below 50 and upto 600 however the range of the above 100 to 450 contains little date of graphical value to I would like to know of some way of displaying the 0 - 50 data in detail, missing the range 100 - 450 out then displaying the data from 450 - 600.

I've seen this done in the past where a line runs through the chart expressing that the specified data range is unchanging for that range but I cant figure out how to do it.

I'm sorry if I'm not being very clear here, its quite hard to descibe.

Thanks in advance.

Oddball


No luck finding an answer? You could always try Google.