Variable Row/Consistent Column, Table update Question

[CrashBandicoot]

Hey All,

First huge fan of the site, been ridiculously helpful thus far. I finally hit a problem I hadn't found a thread for, and decided to join the community.

Second, the problem...

Background:

I have used various frankenstiened pieces of code/recorded Macros/ and coded a bit to make a pretty solid tracking document for a task I deal with at work. I have one goal left that I've hit a brick wall with, and I have a feeling that it could be done with the proper knowledge of coding, but as I have yet to delve into the real world of VBA coding I am at a loss. I am currently struggling to make an update sheet button that does the following things:

- Selects the contents of a cell (C14) on sheet 'Alpha'
- Uses the contents of the Cell (C14) 'sheet Alpha' to find the exact same contents in a cell always in (Column I) of sheet 'Beta'
--The contents being searched for is unique, the column is consistent, but because people are consistently adding rows to the document the row number varies.

-- Within the varying row on sheet 'Beta' that holds the cell that has the contents matching the information in C14 Alpha:
--Update the following other columns within the active row using:
--- Cell C16 on sheet 'Alpha' copy paste values to Cell A (active row number) sheet 'Beta'
--- Cell E28 on sheet 'Alpha' copy paste values to Column R (active row number) sheet 'Beta'
--- Cell G13 on sheet 'Alpha' copy paste values to Cell T (active row number) sheet 'Beta'

--- After updating sheet Beta, with Alpha sheet values
----clear contents of columns C,E,G on sheet Alpha

Would love to see how you guys approach this is if it is even possible.

Thanks for any and all help you maybe able to offer.

Best,

CrashBandicoot

 

[My Aswer Is This]

Try this:

Now at the end I only cleared the used ranges and not the entire columns
You said:
"clear contents of columns C,E,G on sheet Alpha"
But I figured you only wanted:
"Sheets("Alpha").Range("C14,C16,G13,E28").ClearContents"

If you want entire columns cleared let me know.

Sub My_Find_New()
Application.ScreenUpdating = False
On Error GoTo M
Dim Lastrow As Long
Lastrow = Sheets("Beta").Cells(Rows.Count, "I").End(xlUp).Row
Dim SearchString As String
Dim SearchRange As Range
SearchString = Sheets("Alpha").Range("C14").Value
Set SearchRange = Sheets("Beta").Range("I1:I" & Lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
SearchRange.Offset(0, -8).Value = Sheets("Alpha").Range("C16").Value
SearchRange.Offset(, 9).Value = Sheets("Alpha").Range("E28").Value
SearchRange.Offset(0, 11).Value = Sheets("Alpha").Range("G13").Value
Sheets("Alpha").Range("C14,C16,G13,E28").ClearContents
Application.ScreenUpdating = True
Exit Sub
M:
MsgBox "The value  " & Sheets("Alpha").Range("C14").Value & "  was not found"
Application.ScreenUpdating = True
End Sub

 

[CrashBandicoot]

Hi My Answer Is This,

First, I tested out your code verbatim and it works fantastic. I should be able to adapt it to my actual spreadsheets fairly easily. So thank you! Second, I was wondering if you could help me figure out one last thing.

Same gig as before basically, but what if I have two additional cells that only sometimes contain a value?

Is there a way you might be able to help me work out something out that:

- checks cell k19 (sheet alpha)
- if blank does nothing
- if not blank copies contents to sheet beta column o, (active row range)

-checks cell r20 (sheet alpha)
- if blank does nothing
- if not blank copies contents to sheet beta column p, (active row range)

Thanks again for all your help and time with this.

Best,
Crash

 

[CrashBandicoot]

Wait would I just add:

Skipblanks = True?

 

[CrashBandicoot]

Figured it out.

If IsEmpty

no need for anything else!! Sorry for spamming this just didn't want to waste anyone's time.

 

[My Aswer Is This]

Glad you know how to modify script to your needs. It's always nice to see users trying to learn and not just looking for answers.
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.