TransRlucent
New Member
- Joined
- Dec 12, 2018
- Messages
- 2
I'm a bit new to VBA. But, I've gotten pretty far into this project at work. However, I've come across a logic problem that I don't have a knowledge base wide enough to complete:
The problem goes like this:
I have many Work books that I need to export to a master WB one at a time (this wb then processes the information through a large array of logic gates) which then are printed off in a summary of the information (this part is already done). However, in order to make the needed training for this workbook minimal, I'd like to incorporate a macro that goes into a folder (where the wbs in need of processing will go) to which the macro then cycles through each wb one after another, processes the information, and prints each of the processed and summarized information out as separate forms.
Most of this is done. However, the part I'm stuck on is the cycling through worksheets in each workbook and adjusting the margins of the top of the ws to a max of 4 rows distance between a cell containing the text "ID #" and the top of the ws (row A).
I also only have a testing copy of my macro (the big cats told me I can't take my work home):
Code:
Again, I'm new to this so this is just a patchwork of other peoples codes that's close to what I want to do.
The worksheets I need to measure sometimes come in with 1, 2, or 3 rows between it and the row containing "ID #", so I would need a macro that adjusts that distance to 4 no matter if it is 1, 2, or 3 in the unaltered version.
I know this is an If Then command. But for some reason I can't wrap my head around how to find a distance and then tell the macro to make the distance 4.
Thank you in advance to any insight.
The problem goes like this:
I have many Work books that I need to export to a master WB one at a time (this wb then processes the information through a large array of logic gates) which then are printed off in a summary of the information (this part is already done). However, in order to make the needed training for this workbook minimal, I'd like to incorporate a macro that goes into a folder (where the wbs in need of processing will go) to which the macro then cycles through each wb one after another, processes the information, and prints each of the processed and summarized information out as separate forms.
Most of this is done. However, the part I'm stuck on is the cycling through worksheets in each workbook and adjusting the margins of the top of the ws to a max of 4 rows distance between a cell containing the text "ID #" and the top of the ws (row A).
I also only have a testing copy of my macro (the big cats told me I can't take my work home):
Code:
Code:
Sub TEST_LOOP()
Dim rng As Range, _
rng1 As String
Range("A1").Select
Set rng = Cells.Find(What:="ID #", After:=ActiveCell, LookIn:=xlFormulas, _
lookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If rng = 1 Then
rng1 = rng.Address
Do
rng.Offset(-1, 0).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Set rng = Cells.FindNext(rng)
Loop While Not rng Is Nothing And rng.Offset(-1, 0).Address <> rng1
End If
End Sub
Again, I'm new to this so this is just a patchwork of other peoples codes that's close to what I want to do.
The worksheets I need to measure sometimes come in with 1, 2, or 3 rows between it and the row containing "ID #", so I would need a macro that adjusts that distance to 4 no matter if it is 1, 2, or 3 in the unaltered version.
I know this is an If Then command. But for some reason I can't wrap my head around how to find a distance and then tell the macro to make the distance 4.
Thank you in advance to any insight.