Posts by bobdokey

Re: Return Words Before, After, and Between Periods and Commas into Separate Cells

This worked wonderfully!! Thank you!

I have a several large worksheets that contain a "Remarks" column, and I need to separate the statements into separate cells to better analyze. For instance:

In Cell A1:
Open the well. Assembled the unit. Run in the well with 1 x Osiris Sensor, 1 x 80 H/ 2155 v/ 24 A High Temp Motor, 2 x 98L-HL-HT Seals, 1 x TD-1750 - 24 Stgs Pump w/Intake, 1 x TD-850 - 211 Stgs Pump, 1 x 6' x 2 3/8" SS Sub w/Collar, 244 joints of 2 3/8"

I need every individual statement (ex. "Open the well","Assembled the unit","244 joints of 2 3/8"") extracted and entered into cells B1 through ?? (However many cells needed, depending on the amount of information within cell A1)

A macro would work best I'd think. But i'm stumped so I'm open to any suggestion.

Is this possible?

Thanks in advance..

Re: Including format of item or copying item to paste it later.

Thanks cytop, I didn't see his response on that second page. I will work with it in the morning and reply with a post to that thread.

I am trying to copy the ".item" but i'm not sure if its possible. Referencing the .item doesn't pull in the format style of the cells, so I need to figure out where to copy the item in this code, and where to paste it.

Sub FillVariables()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet
a = Sheets("Sheet3").Range("A1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
If Not .exists(Trim(a(i, 1))) Then .Item(Trim(a(i, 1))) = Trim(a(i, 2))
Next
For Each ws In Worksheets
If ws.Name = "Sheet2" Then
For Each rCell In ws.Columns("I").SpecialCells(2, 2)
If .exists(Trim(rCell.Value)) Then rCell.Offset(, 1) = .Item(rCell.Value)
Next
End If
Next
End With
Set rCell = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

Re: Modifying Code to reference cells AND it's formatting.

That ALMOST did it!!!! Dang, so very close. It did exactly as you said it would but since I didnt explain what my code is doing, it copied from the wrong sheet(Sheet2), instead of (Sheet1).
Thanks for your help cytop. Let me explain my process better.
The macro is searching inside "Sheet2, Column A" to find any variables from "Sheet1, Column A". When it finds a match, it is taking the info from the right(or Col.B) of "Sheet1, Column A" and puting it to the right of "Sheet2, Column A"...IF theres nothing already in the cell.
I basically want to do this same thing with the formatting. And, based on what you've showed me, if I could just know what to copy inside the code, then it should work, hypothetically.

My thoughts are, it is item that we are wanting to copy.. but I don't quite know how.

Re: Modifying Code to reference cells AND it's formatting.

Don't know if it will help you understand but I'm getting a runtime error on this line ".Item(rCell.Value).Copy" saying "Object Required".

Re: Modifying Code to reference cells AND it's formatting.

I'm not very good with vba, I only helped write this code. I'm not sure what you mean cytop, "in-line If Then Statements".. I replaced the line "If (.exists(Trim(rCell.Value)) And rCell.Offset(, 1).Value = "") Then rCell.Offset(, 1) = .Item(rCell.Value) " with your code. Is there something else I need to delete?

Re: Modifying Code to reference cells AND it's formatting.

I tried inserting the

rcell.copy 
rcell.offset(,1).PasteSpecial Paste:=xlPasteFormats

into the my macro but couldn't figure out which thing to actually copy. It didn't seem to work the way you had it typed out but I love the idea. Is this possible?
Could someone show me which part of my code to place the "copy" and which place to place the "PasteSpecial Paste:=xlPasteFormats"?
Please? Thank you.

I am attempting to modify the code below to, not only reference the ".Item(rCell.Value)", but also include it's formatting. The format for that referenced cell will not necessarily be a "certain" format..so I don't want to have to write out what specific formatting needs to happen. When this data from the cell is referenced, I really need that specific cell's formatting to go with it. I hope this makes sense.
The line I'm trying to modify is this one:

If (.exists(Trim(rCell.Value)) And rCell.Offset(, 1).Value = "") Then rCell.Offset(, 1) = .Item(rCell.Value)

The entire code is as follows:

Sub FillVariablesOnlyBlanks()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet


a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
If Not .exists(Trim(a(i, 1))) Then .Item(Trim(a(i, 1))) = Trim(a(i, 2))
Next
For Each ws In Worksheets
If ws.Name = "Sheet2" Then
For Each rCell In ws.Columns("A").SpecialCells(2, 2)
If (.exists(Trim(rCell.Value)) And rCell.Offset(, 1).Value = "") Then rCell.Offset(, 1) = .Item(rCell.Value)
Next
End If
Next
End With
Set rCell = Nothing
Set ws = Nothing
Application.ScreenUpdating = True


End Sub

Re: Returning only cells with data from a range. Code needs modified.

To be clear, I implented it like this:
Sub TESTFillVariables()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet
Dim s$

a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
s = Trim(a(i, 1))
If Len(s) Then If Not .exists(s) Then .Item(s) = Trim(a(i, 2))
Next
For Each ws In Worksheets
If ws.Name = "Sheet2" Then
For Each rCell In ws.Columns("A").SpecialCells(2, 2)
If .exists(Trim(rCell.Value)) Then rCell.Offset(, 1) = .Item(rCell.Value)
Next
End If
Next
End With
Set rCell = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

And it ran without errors but still pasted blanks instead of skipping them.

Re: Returning only cells with data from a range. Code needs modified.

Sorry, but i've tried putting it in there in place of things and also combined with but I cant seem to get it to work. Where should I put this inside of that code? Thank you so much for your help!

This code works perfectly for me but I need it modified. Currently it returns a value from this line:
"If Not .exists(Trim(a(i, 1))) Then .Item(Trim(a(i, 1))) = Trim(a(i, 2))"
even if it has nothing in the cell, it will overwrite whats there, making the destination cell blank. I have info in the destination cells that I would like to show up ONLY if the "Trim(a(i, 2))" is blank.
Basically i'm needing this code to preform the idea of paste and skip blanks...while not messing up it's it's complex match functioning.
I'm not very good at VBA, this was a code made for me on this website and I'd really appreciate help with this, as it will help me with my work tremendously.

Sub TESTFillVariables()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet
a = Sheets("Sheet1").Range("A1").CurrentRegion.Value


With CreateObject("Scripting.Dictionary")
.CompareMode = 1
For i = 2 To UBound(a, 1)
If Not .exists(Trim(a(i, 1))) Then .Item(Trim(a(i, 1))) = Trim(a(i, 2))
Next
For Each ws In Worksheets
If ws.Name = "Sheet2" Then
For Each rCell In ws.Columns("A").SpecialCells(2, 2)
If .exists(Trim(rCell.Value)) Then rCell.Offset(, 1) = .Item(rCell.Value)
Next
End If
Next
End With
Set rCell = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
 
End Sub

I have a list of ID numbers in Column A("List Sheet") that I need to find throughout a workbook.
I have a list of the corresponding ID numbers in Column B("List Sheet").
In Column D through J there's a list of the Corresponding ID number and it's fields.

I am needing to find in sheets "1 through etc.." the ID numbers from Column A("List Sheet") and insert the corresponding ID numbers and it's fields(Columns D-J "List Sheet")..insert them(not paste them) into a new row below the found ID number.

I've attached an example workbook to help explain.

forum.ozgrid.com/index.php?attachment/47216/

I'm referencing my previous post because "Dangle" was able to do something similar.. but this one takes that one and complicates it massively.

I'm needing this at my work and this macro would save me months of time because of how much data I have.Thank you everyone for your help!

http://www.ozgrid.com/forum/sh…php?t=164606&goto=newpost**

Re: A Macro For Various Data Corrections From List

Like. A. Charm.

Thanks again!!

Re: A Macro For Various Data Corrections From List

Thank you. I'd forgotten I used that for another project.
I corrected that and it ran fine but now it is completely clearing out Column M from all worksheets other than "List Sheet". It didn't empty Columns (M) ONLY IF Columns (i) matched Column (B).

Re: A Macro For Various Data Corrections From List

Thanks for getting back so quickly.
I tried the code and it ran without errors but did not change the matched data cells(Matching Column B with Columns i). It didn't empty Columns (M) if Columns (i) matched Column (B).

Re: A Macro For Various Data Corrections From List

I need this to function exactly as it is currently with one addition. If it finds any cell in collumn i(ws-<>"List Sheet") any cell from Column (i) that that MATCHES the data from Column B(ws-List Sheet), I want it, instead of filling the cell in Column M with the contents form Column B(ws-List Sheet) like it does now, I need it to erase the contents in Column M and input an EMPTY cell..only for the ones that Match the data from Column B.

Basically I just need to figure out how to add the phrase "If does match Column B from ws-"List Sheet" then return an empty cell into Column M(every sheet not equal to "List Sheet")".

Sub Variables()
Application.ScreenUpdating = False
Dim a, i As Long, rCell As Range, ws As Worksheet
a = Sheets("List Sheet").Range("A1").CurrentRegion.Value
With CreateObject("Scripting.Dictionary")
.comparemode = 1
For i = 2 To UBound(a, 1)
If Not .exists(Trim(a(i, 1))) Then .Item(Trim(a(i, 1))) = Trim(a(i, 2))
Next
For Each ws In Worksheets
If ws.Name = "SAP_EQ_Master" Then
For Each rCell In ws.Columns("I").SpecialCells(2, 2)
If .exists(Trim(rCell.Value)) Then rCell.Offset(, 4) = .Item(rCell.Value)
Next
End If
Next
End With
Set rCell = Nothing
Set ws = Nothing
Application.ScreenUpdating = True
End Sub

Does this makes sense?

Re: A Macro For Various Data Corrections From List

You are awesome and you just saved me weeks of time. Thank you so much!!

Re: A Macro For Various Data Corrections From List

forum.ozgrid.com/index.php?attachment/45396/Great!! Thank you for your optimism.
I hope the attachment works.

Collumn A(List Sheet) is incorrect. Collumn B(List Sheet) is the intended value.
I need to search a different collumn in a different sheet, collumn I(Sheet 1-Etc...) for values from Collumn A(List Sheet) across multiple sheets. When found, I need the corrected term from Collumn B(List Sheet) to be inserted into Collumn M(Sheet 1-Etc...)

Thanks so much for your help.forum.ozgrid.com/index.php?attachment/45393/

I have a list of incorrectly spelled or formatted data and also a list of that data corrected. Two lists on one sheet.
I need to search across multiple other sheets for the "incorrect data" and place the correlating "corrected" cell from the corrected list, into the cell next to it.

Example:

Collumn I(Sheet1) is incorrect. Collumn M(Sheet1) is correct.
I need to search a different collumn, collumn "B" for values from Collumn I(Sheet1) across multiple sheets for each individual value of Collumn I(Sheet1).. and when found, I need the corrected term from Collumn M(Sheet1) to be inserted into Collumn C(Multiple Sheets).

I could really use the help, I've been on this for days doing it individually and without a macro it may take weeks.