#1
|
|||
|
|||
"For Each" with arrays
Hey all,
I am trying to pull header info from a sheet and put it into a destination sheet. The info is an alphanumeric part number. I am trying to put all part numbers that end in 2 in one cell, and all other part numbers in another (there are only two possible part numbers on my source sheet.) I am having issues with the "For Each" part of my VBA: Code:
For x = 1 To wkshtcount Set sh = ThisWorkbook.Sheets(x + 1) Set shdest = ThisWorkbook.Sheets(sh.name & (x + 1)) Set rn = sh.UsedRange Set Names = sh.Range("A2:A" & Range("A1").End(xlDown).Row) For Each name In Names If name Like "*2" Then shdest.Range("D7") = name Else: shdest.Range("D8") = name End If Next name Next x I think the issue might be that it is not looping through Each name in Names. What could be the reason for this? |
#2
|
|||
|
|||
Hi,
Can you attach the file so can see it? This may help. |
#3
|
|||
|
|||
I am still confused as to why my For Each didn't work, but I solved this by getting rid of my "For Each" and replacing it with the following:
Code:
Set rn = sh.UsedRange k = rn.Rows.Count + rn.Row - 1 For y = 2 To k If sh.Range("A" & y) Like "*2" Then shdest.Range("D7") = sh.Range("A" & y) Else: shdest.Range("D8") = sh.Range("A" & y) End If Next y |
#4
|
|||
|
|||
For Each
Hi,
Here' a bit of code using "For Each". I found this doing a search. If this does not help can you proved a workbook so we can better help you. Code:
Sub WorksheetLoop() Dim WS_Count As Integer Dim I As Integer ' Set WS_Count equal to the number of worksheets in the active ' workbook. WS_Count = ActiveWorkbook.Worksheets.Count ' Begin the loop. For I = 1 To WS_Count ' Insert your code here. ' The following line shows how to reference a sheet within ' the loop by displaying the worksheet name in a dialog box. MsgBox ActiveWorkbook.Worksheets(I).Name Next I End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help with conditional formatting & returning "Pass" / "Fail Results" in a Column N | mikey386 | Excel | 2 | 12-11-2014 01:14 PM |
remove repeated words with " macro " or " wild cards " in texts with parentheses and commas | jocke321 | Word VBA | 2 | 12-10-2014 11:27 AM |
How to edit the "Format" and the "show level" of an EXISTING table of content? | Jamal NUMAN | Word | 2 | 08-14-2011 10:46 AM |
How to choose a "List" for certain "Heading" from "Modify" tool? | Jamal NUMAN | Word | 2 | 07-03-2011 03:11 AM |
"Microsoft Excel Application" missing in the "Component Services" on win08 | sword.fish | Excel | 0 | 02-26-2010 02:09 PM |