Posts by turtle44

Re: Find Cell Value In Another Workbook & Copy

Quote from Sweater Fish Delux

I guess this just points to some fundamental misunderstanding of VBA on my part since I can't figure out any reason why the second code doesn't work as desired.

I think it should work either way.

Quote from Sweater Fish Delux

Also, you mentioned the possibility of an empty cell in column C where I'm trying to retrieve data. How could I catch those occurances as well? rFound doesn't remain set to Nothing in those cases? What is it set to?

It is set to the empty cell in Column C. What do you want to happen if "Application ID" is found in column B but column C is blank?

Re: Finding The First Empty Row In A Macro

This will find the last row with any data. (Add 1 to get next empty row) There is no error handling, so this will fail if entire sheet is blank.

lLastRow = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, Lookat:=xlPart, _
         SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

Re: Finding The First Empty Row In A Macro

Are you trying to find the next row after the end of your data, or do you have blank rows in between your data?

Re: First Alphabetical Text In Range

I knew I was over-complicating it. Why would I use Min when I know the min value is always 0? Not very bright.

Re: First Alphabetical Text In Range

I'm probably over-complicating the problem, but this will work.
[bfn]=INDEX($B$1:$B$20,MATCH(MIN(COUNTIF($B$1:$B$20,"<" & $B$1:$B$20)),COUNTIF($B$1:$B$20,"<" & $B$1:$B$20),0))[/bfn]
Array formula confirmed with Control-Shift-Enter

Re: Finding The First Empty Row In A Macro

Quote from chatguy

Would anyone know how to search for the "First Empty Row" in a VB Macro?

The easiest way would be to look at the "Possible Answers" above. I'm sure they answer the same question. A simple search would work also.

Re: Too Many Arguments

If you table data is in D1:E14, then enter this formula in B1 and copy down.
[bfn]=VLOOKUP(A1,$D$1:$E$14,2,FALSE)[/bfn]
Or, if you don't want the error values, something like this:
[bfn]=IF(COUNTIF($D$1:$D$14,A1)=0,"",VLOOKUP(A1,$D$1:$E$14,2,FALSE))[/bfn]

Re: Too Many Arguments

Then just use a Vlookup.

Re: Macro Autofill Row until no more adjacent values

If by "adjacent cells", you mean column A, then use my code above with the following change:

lLastRow = Range("A" & Rows.Count).End(xlUp).Row

Re: Creating Copy Of Worksheet

As Will pointed out, it would be extremely difficult for anyone to tell you what's wrong with your code since you haven't posted your code.

Re: Find A Value In A Column, And Replace The Value In The Adjacent Column, Same Row

You're welcome.

Re: Too Many Arguments

You can only nest 7 If functions. What are you trying to do exactly?

Re: Combine Macro And Autofill Row

Not sure what you're asking, but see if this helps:

Dim lLastRow As Long
lLastRow = 101 'how do you determine last row???
Range(Cells(2, "B"), Cells(lLastRow, "B")).FormulaR1C1 = "=MID(RC[1],34,20)"
Columns("B:B").EntireColumn.AutoFit
Range(Cells(2, "A"), Cells(lLastRow, "D")).Sort Key1:=Range("B2"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Also, are you really using Excel 2007, because I didn't think FileSearch was compatible with that version?

Re: Find A Value In A Column, And Replace The Value In The Adjacent Column, Same Row

Each time you search, you are starting at the beginning. So even though you are looping for each instance, you are only finding the first instance each time. Give me a few minutes and I'll give you a fix.[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Here you go:

Sub Update_Product_IDs()
   Dim iCount As Integer
   Dim iPMD_Name As Integer
   Dim rFound As Range
   Application.ScreenUpdating = False
   'For each *.PMD imported, Find and Replace the dimensional ProdID with the TJID that will  import into Stellar
   For iPMD_Name = 2 To Worksheets.Count
      Worksheets(iPMD_Name).Select
      With Worksheets(iPMD_Name).Range("L:L")
         Set rFound = .Cells(1, 1)
         'Find the cell reference, go to the preceding column, and input 113010 for 2X4
         For iCount = 1 To WorksheetFunction.CountIf(Range("L:L"), "2 X 4*")
            Set rFound = .Find(What:="2 X 4*", After:=rFound, LookIn:=xlFormulas, _
                  LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                  MatchCase:=False, SearchFormat:=False)
            rFound.Offset(0, -1) = "113010"
         Next iCount
      End With
      Cells.EntireColumn.AutoFit
      Range("A1").Select
   Next iPMD_Name
   Application.ScreenUpdating = True
End Sub

Re: Find Word In Column, Set Formula Based On Response

[bfn]=IF( COUNTIF(B:B,"*Resevoir*")>0,SUM(D7:D257)*0.1,0)[/bfn]

Re: Vba Ddeclaring File.path Ans Todays Date As Constants

Quote from Sir Eddy

Do I have to re-declare these values for each sub-procedure?

No, just use a global variable.

Quote from Sir Eddy

I just noticed the typo in my thread title, which I can't edit.

Sure you can.

Re: Find A Value In A Column, And Replace The Value In The Adjacent Column, Same Row

Please post your existing code that changes the first instance.

Re: Find Word In Column, Set Formula Based On Response

No need for code, just enter the following formula in C2.
[bfn]=IF(COUNTIF(B:B,"Resevoir")>0,SUM(D7:D257)*0.1,0)[/bfn]

Re: Find Cell Value In Another Workbook &amp; Copy

Works fine when I test it. Are you sure it's not finding anything? Are you resetting the value of rFound to nothing? Are you perhaps finding a match in column B, but there is no data in column C?

Re: Conditional Importing Of External Cells

What is value of DestColumn? If it is a cell at the bottom of your sheet, your code should work. Otherwise, I assume that is your problem.