Thread: Macro to edit and enter various, changing data.

Hello Everyone,

I have come across a problem with my spreadsheet that generates the schedule for components and sub-components. I receive a file that I copy and paste special - values only. It looks like in Sheet1 but a lot longer. what I have to do next to make sure that the formula on the Sheet2 picks all the requirements is F2 and Enter every single cell with Part Number in column A and also row 4 as from A4 to AA4 to edit and enter all the dates.

Then I need to do exactly the same with all cells containing data (figures) in columns M,N,O,P,Q and R from row 5 down.

I was trying to avoid this now as the spreadsheet has grown significantly bigger and come out with the macro to do this for me. the only problem with macros I have is that when I record it to do on the blank cell (as the Part Numbers shown on the schedule may vary from week to week) it will always come back blank. When recording it with the figures it only saves a numer that is at this one time and no matter what else you put in there it will still come back with the original number as this is what it has remembered.

I have also made a macro that copies and pastes values only but this is not good at all as the formula doesn't pick it up at all.

I attached a sample file to look at with macros recorded.

Anybody has an idea how manipulate the macro to get the result as per 150110 part please share.

Thank you for giving it a go.

Simon

When the Sheet1 values in A7, A9 and A11 are converted to numbers (they are currently text strings that look like numbers), then the table on sheet2 starts to work for those items, too.

Hello,

Thank you for your hint. I have tried your suggestion but it didn't work. Would it be the reason that I work on the 2000 version of Microsoft Excel?

I still cannot crack it how to start of blank and get back with figures in the same cells.

Thanks

This is not an Excel version thing. Your data in column A needs to numeric (not just look numeric) for the formulas on the second sheet to work.

I just clicked on A7, pressed F2, then Enter. Repeat with A9, A11, and that turned those values into numbers. Then Sheet2 displayed values from those rows.

Hi,

Thank you for your input into this case. I have done what you said about changing the data to a numeric format but it didn't pick it up (i have changed it by going to 'format cells').

I do agree that it works by F2 and ENTER but because the schedule is so long now I wanted to save myself some time by doing it with a press of a button and a calculation to take a minute or two.

I am still trying to work it out but could use any help.

Regards

Hi again,

I have checked when I do F2 and ENTER and it comes to be:

Sub edit()
'
' edit Macro
' Macro recorded 08/02/2012 by szymond
'

'
    ActiveCell.FormulaR1C1 = "150110"
    Range("A8").Select
End Sub

But because A8 which is a first cell in the real schedule spreadsheet contains '150110' this is what stays in there.
My question is (and still cannot find the answer) if I can change this "150110" to an A8 cell itself. So it is picking up whatever is in the cell anytime I paste anything to it.

I am also trying to see if it is possible to go for the whole range of A8:R300 or something like this or do I have to do it cell by cell.

Thanks for giving it a go.

Simon

Something like this may help:

Sub ReassertValues()
Dim RNG As Range, cell As Range

Set RNG = Range("A5:A100").SpecialCells(xlConstants)

    For Each cell In RNG
        cell.Value = cell.Value
    Next cell

End Sub

Hi,

Thank you for your help.

I am just about to implement the suggestion of yours. The only question I have is when you say for each cell in the range it means that I need to go cell.Value = cell.Value as Range("A5").Value = Range("A5").Value?

I am trying this any way and will see what happens.

Will let know if works.

Thanks

Simon

No, the macro as given would fix all the column A values for you. You can edit this line to the range of cells you want to fix:

Set RNG = Range("A5:A100").SpecialCells(xlConstants)

Hi JBeaucaire ,

Thank you greatly for your help. Ihave tried first with the whole range not cell by cell as in my question and yes it worked perfectly. It does what it is intended to do and now my spreadsheet works absolutely fine. The only thing that is left is the time it takes to run 1:15' which I am trying to reduce by minimising the range to only the necessery cells.

Thank you once again for you input.

Simon

The technique shown is editable, so you can specify the range needed. Also, this technique will only work on cells within the range that have an actual value in them. I think this is good, all things considered.