|
FIND-COPY DIFFERENT WORDS WITH MACRO
Hi,
I am trying to create a macro that will find the word "MONDAY" in column A, then copy "MONDAY" (in every cell down to column A) and stop when it will find the word "TUESDAY" and so on. Can someone help me with this? Thanks for your help!!! Spyros |
FIND-COPY DIFFERENT WORDS WITH MACRO
One solution:
Sub findday() srcdir = xlNext shiftback = 1 days = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "*") For d = 1 To UBound(days) - 1 Columns("A:A").Select On Error Resume Next currdayrow = Selection.Find(What:=days(d), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then For nd = d + 1 To UBound(days) On Error Resume Next If nd = UBound(days) Then srcdir = xlPrevious Cells.Select shiftback = 0 End If nextdayrow = Selection.Find(What:=days(nd), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=srcdir, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then Range("A" & currdayrow + 1 & ":A" & nextdayrow - shiftback).Value = days(d) Exit For End If Next nd End If Next d Range("A1").Select End Sub Regards, Stefi Spiros ezt *rta: Hi, I am trying to create a macro that will find the word "MONDAY" in column A, then copy "MONDAY" (in every cell down to column A) and stop when it will find the word "TUESDAY" and so on. Can someone help me with this? Thanks for your help!!! Spyros |
FIND-COPY DIFFERENT WORDS WITH MACRO
stefi,
if i have numbers than days. For example: 10.000, 20,000,30,000 ... Ο χρήστης "Stefi" *γγραψε: One solution: Sub findday() srcdir = xlNext shiftback = 1 days = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "*") For d = 1 To UBound(days) - 1 Columns("A:A").Select On Error Resume Next currdayrow = Selection.Find(What:=days(d), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then For nd = d + 1 To UBound(days) On Error Resume Next If nd = UBound(days) Then srcdir = xlPrevious Cells.Select shiftback = 0 End If nextdayrow = Selection.Find(What:=days(nd), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=srcdir, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then Range("A" & currdayrow + 1 & ":A" & nextdayrow - shiftback).Value = days(d) Exit For End If Next nd End If Next d Range("A1").Select End Sub Regards, Stefi Spiros ezt *rta: Hi, I am trying to create a macro that will find the word "MONDAY" in column A, then copy "MONDAY" (in every cell down to column A) and stop when it will find the word "TUESDAY" and so on. Can someone help me with this? Thanks for your help!!! Spyros |
FIND-COPY DIFFERENT WORDS WITH MACRO
Just replace line
days = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "*") by days = Array(10000, 20000, 30000, 40000, 50000, 60000, 70000, "*") Regards, Stefi Spiros ezt *rta: stefi, if i have numbers than days. For example: 10.000, 20,000,30,000 ... Ο χρήστης "Stefi" *γγραψε: One solution: Sub findday() srcdir = xlNext shiftback = 1 days = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "*") For d = 1 To UBound(days) - 1 Columns("A:A").Select On Error Resume Next currdayrow = Selection.Find(What:=days(d), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then For nd = d + 1 To UBound(days) On Error Resume Next If nd = UBound(days) Then srcdir = xlPrevious Cells.Select shiftback = 0 End If nextdayrow = Selection.Find(What:=days(nd), After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=srcdir, _ MatchCase:=False, SearchFormat:=False).Row If Err = 0 Then Range("A" & currdayrow + 1 & ":A" & nextdayrow - shiftback).Value = days(d) Exit For End If Next nd End If Next d Range("A1").Select End Sub Regards, Stefi Spiros ezt *rta: Hi, I am trying to create a macro that will find the word "MONDAY" in column A, then copy "MONDAY" (in every cell down to column A) and stop when it will find the word "TUESDAY" and so on. Can someone help me with this? Thanks for your help!!! Spyros |
| All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com