Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. dwilson01

    auto fomatting

    So, did you figure out the reversal of sheets? Here is a fix for the fill handles problem, basically what it does is check to see if the target address is more than a single cell, if it is, the code is skipped. This works in my test sheet, if this doesn't work for you, I don't understand the...
  2. dwilson01

    auto fomatting

    Sheet 1 may look like this Col 1 Col 2 Col 3 NAME ID-NAME ID-PARTNER Tommy 4875125-TOMMY 4875126-JOHN John 4875126-JOHN 4875125-TOMMY Kate 4875127-KATE 4875128-SHARRON Sharron 4875128-SHARRON 4875127-KATE tim 4875129-TIM 4875130-RYAN RYAN 4875130-RYAN 4875129-TIM terry...
  3. dwilson01

    auto fomatting

    I found a way to do what it is I think you want to do. With a similar setup as my last post, i.e. sheet 1 contains the NAME, ID-NAME and ID-PARTNER. Then typing the NAME in column 1 of sheet 2, the code will search sheet 1 for a match and if found will change the name typed in sheet 2 to the...
  4. dwilson01

    auto fomatting

    This may not be exactly what you are looking for, but it may be useful to you. I attempted to solve your problem using the VLOOKUP worksheet function. The only problem I ran into is that I could not get the cell that you type the name into to update with the ID-Name. I used sheet 1 to set a...
  5. dwilson01

    Dynamic Table Cell References

    ...= 2 Then iRow = Target.Row If Cells(iRow, 1) <> &quot;&quot; And Cells(iRow, 2) <> &quot;&quot; Then Cells(iRow, 3) = Cells(iRow, 1) * Cells(iRow, 2) ActiveSheet.Range(Cells(iRow + 1, 3).Address) _ .Formula = &quot;=SUM(C1:C&quot; & iRow & &quot;)&quot; End If End If End...
  6. dwilson01

    Dynamic Table Cell References

    ...easy, try ActiveCell.Row Formulas using columns to the right, try iRow = ActiveCell.Row iCol = ActiveCell.Column YourCell = Cells(iRow,iCol + 2) * Cells(iRow,iCol + 4) Using this information can be tricky because the data is being entered row by row and column by column. This could cause...
  7. dwilson01

    Sum in Excel - VBA

    You could always use the code that takes you to the last row in a worksheet (65536) and use Selection.End(xlUp).Select. In other words, for this problem Cells(65536,21).Select Selection.End(xlUp).Select tRow = Selection.Row This method doesn't always work when you have non-contiguous data and...
  8. dwilson01

    changing font colours in adjcent cells

    This does what you are asking, it could probably be cleaned up for you specific needs. Anyway, here it is ActiveSheet.UsedRange For Each oCell In ActiveSheet.UsedRange.Columns(1).Cells Range(Cells(oCell.Row, oCell.Offset(0, 1).Column), _ Cells(oCell.Row, oCell.Offset(0...
  9. dwilson01

    Sum in Excel - VBA

    Carie, The worksheet functions work using ranges for the arguments, therefore, you have to define teh range you want to sum. If the data is in column 21 (Q) then you could use the following: To enter a formula in the cell use: ActiveSheet.UsedRange tRow = ActiveSheet.UsedRange.Rows.Count...
  10. dwilson01

    How do you determine whether an excel workbook is open?

    LeighAnne, There is only one little thing that I saw as a possible problem with Geoff's code, and that is the wb.name doesn't give the path. This would cause the routine to never find a match for the open workbook and strSheet. Geoff's code works great in both excel 97 & 2000 with a slight...
  11. dwilson01

    Sorting data using R1C1 in a spreadsheet

    This works if the persons name is in the active cell and the score is in the next column to the right. Rng = ActiveCell.Address & &quot;:&quot; & ActiveCell.Offset(9, 1).Address Range(Rng).Select Selection.Sort key1:=Range(ActiveCell.Offset(0, 1).Address)
  12. dwilson01

    Finding the week number

    The next/current week number would be nxtWeekNo = Format(Now(), &quot;ww&quot;) + 1 curWeekNo = Format(Now(), &quot;ww&quot;) dwilson01
  13. dwilson01

    Copying data to separate workbook

    I'm in training today but this should get you started. Private Sub Worksheet_Change(ByVal Target As Range) 'May want to check to see if second workbook is open here For Each wb In Application.Workbooks If wb.Name = &quot;Your Workbook Name&quot; Then GoTo exitloop Next Workbook.Open...
  14. dwilson01

    How do you check for records in one spreadsheet in another?

    You could use the .Find, it is very well documented in teh help. If you need some code, let me know. dwilson01
  15. dwilson01

    Copying data to separate workbook

    You are correct in your thoughts, however, the change event may not be the best place to put your code. You may want to put it in Workbook_BeforeClose(Cancel As Boolean). This will allow for multiple changes before running any code and interupting the user. Is the copy workbook always going...
  16. dwilson01

    How to know if buttons exist

    To check for the command buttons you can use the following to print the names the immediate window. If you know the names of the buttons. For Each cmd In ActiveSheet.Shapes Debug.Print cmd.Name Next After determining which buttons are there, you can add teh buttons that are not. If you...
  17. dwilson01

    Adding text to a selection of cells..

    You could use an InputBox like this: Dim strText As String strText = InputBox(&quot;Enter text string here&quot;, &quot;Text String&quot;) Selection.Merge Selection.Interior.ColorIndex = 3 Selection.WrapText = True Selection = strText Or you could create a custom form by adding a UserForm to...
  18. dwilson01

    Query regarding Names ( Urgent )

    Yes, the #REF means the reference to the cell(s) has been broken.
  19. dwilson01

    Query regarding Names ( Urgent )

    In the formula ='C:\[A.xls]81'!$HB$8, the 81 represents the sheet name in workbook A. Is the formula working? If so, there must be a sheet named 81 in the file A.xls. Check the properties - contents of workbook A.xls. Point to File - Properties, click Contents, under Worksheets look for 81...
  20. dwilson01

    Query regarding Names ( Urgent )

    The answer to your question is, YES. The following will find and select the cell containing the name &quot;Josh&quot; in the named range &quot;Range1&quot;. Set Rng = Names(&quot;Range1&quot;).RefersToRange Set C = Rng.Find(&quot;Josh&quot;) C.Select

Part and Inventory Search

Back
Top