PDA

View Full Version : [SOLVED] VBA To convert Formula(s) to Value(s) to track serial numbers



geranuno
06-04-2015, 11:21 AM
Hello Everyone! I am new to the forum and I am not a developer (Just a passionate aficionado)

I am currently working on a project to track time of reworks units individually based on their serial numbers.


At this point I did the Index-Match to identify the rework unit, but when I scan another serial number, it erases my previous data.


This can be avoided if I just paste-special-values of the serial number in matter at the corresponding location (Next to the Original input number)


Here's an image for reference:


13602

Any help using formulas or VBA will be greatly appreciated!

Thanks so much :-)

GN

SamT
06-04-2015, 03:37 PM
What?

Sixthsense..
06-04-2015, 09:05 PM
Why the scanning serial no is not picking the blank cell for inputting the new data?

If you correct it then the over writing of previous data will not occur :)

geranuno
06-05-2015, 09:42 AM
It is taking the black cell ...do some Index/Match and then places the value next to the original input Ser#


If at least I had a way, that given Input Data (C2)

GO and find the same serial number of (B) Column
And Paste-Special Value the same serial number, Just next to the Original serial number ... that will make it!

Thanks Sixtsense!

GN

Kenneth Hobs
06-05-2015, 11:08 AM
Attaching a short example workbook would make it easier to help.

I am not sure if you want the formula copied back to the same cell or another. If the later, change the 1st A1 to be the cell that has the formula and the 2nd A1 to be where you want just the value. Of course PasteSpecial can paste xlValues.


Range("A1").Value=Range("A1").value

geranuno
06-05-2015, 02:39 PM
Hum ... That make sense Kenneth :-)

I'll try and return ....

Ciao

GN

geranuno
06-05-2015, 03:47 PM
Hahahaha .... Is amazing how simple ideas provides better results!

I did a work-around with your idea ... and Is Working!

I'll poste the answer soon ... Thanks so much Kenneth Hobs!

GN

geranuno
06-05-2015, 05:10 PM
It's working good once .. But I need help to run the loop ...so I can Scan ... calculate ... Scan again:

Here's my code:
Sub Workbook_Open()

Range("C2").Select
Do
Range("C2").Select
Range("C4:C2000").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("D4:D2000").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False


Range("C2").Select
Loop Until IsEmpty(Cells(iRow, 2))


End Sub


Thanks again to all!

GN

Kenneth Hobs
06-05-2015, 06:13 PM
Be sure to use code tags and paste your code between them. Disable smilies when posting code.

Can you just say what your goal is rather than just posting code? While the macro recorder is a good way to start, it seldom produces optimal code.

e.g.
1.Place the values from C4 down continuously to just before the first blank cell into D4 and down.
or
2. Place the values from C4 and down to the last cell in the column with data with possible blank cells into D4 and down.
or
3. Copy cells with values and formats from ... to ..., etc.

Attaching a simple workbook that illustrates the goal and manually marked result in another sheet can best help us help you.

Case 1 might be:
Sub ken()
Dim r As Range
Set r = Range("C4", Range("C4").End(xlDown))
Range("D4").Resize(r.Rows.Count).Value = r.Value
End Sub

geranuno
06-05-2015, 07:44 PM
Awesome!

Works!

Understood Sir!

Thanks so much Ken .... simple solution!

Have a great weekend!

GN

SamT
06-06-2015, 03:27 PM
Kenneth,

Excellent work.

BTW, What just happened?

:)