Link to home
Start Free TrialLog in
Avatar of nomios
nomiosFlag for Afghanistan

asked on

Simple VB Macro Help

Hi,

I'm pulling data from a SharePoint Survey and moving that data to Excel. I've formatting the entire row in Excel to "General", however, Excel won't change the cells from "text" to "general" until I go into each cell, hit F2 and enter.

I was hoping I could create a Macro to do this for me, but I'm having a little trouble.

Can someone help?

I want the Macro to hit the first cell, B2, and go to the bottom most cell that has data. I simply want it to retain the same information--just switch the "text" to a "General" format.

  With Worksheets("RawData")
    Range("B2:B").Select = "=B2"
    ActiveCell.Activate
   lastrow = .Range("B" & Rows.Count).End(xlUp).Row

Open in new window


Thank you.
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You could try the following steps:

1. Select the entire column B, and set the format to General.

2. Enter the value 0 into an unused cell.

3. Select the cell with the value 0 and copy it.

4. Select the entire column B again and Paste Special.  In the Paste Special dialog, go to the Operation section and select Add, and then click ok.
Avatar of nomios

ASKER

Hi,

The VB macros listed above do not work.

I get this message--

"Object doesn't support this property or method".

Felix--thank you for the reply but I want an automated process to complete this. I run multiple reports daily that have this problem.
On which line do you get the error? The codes look fine to me.
Avatar of nomios

ASKER

Rorya,

Thanks for responding. That error pops up when I run the Macro. It doesn't appear on any of the lines of code.

This is the exact code I have:
  With Worksheets("Quality")
     .Range ("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "General"
    .Value = .Value    

Open in new window

That's not the correct code - your second line should start with "With "
Avatar of nomios

ASKER

Ah, got it.

Okay, so I added it, and, the macro now runs.

However, it's not changing the data properly so formulas still don't function...

I still have to  go into each cell, push F2, then enter to get the cell to format properly.
You could try this code...

Sub Macro1()
    ' Assumptions:
    ' 1. Cell C1 is unused (blank)
    ' 2. Data extends from Cell B2 downwards without blanks in between
    Dim BlankCellRow As Integer ' Row of Blank (unused) cell
    Dim BlankCellColumn As Integer ' Column of Blank (unused) cell
    
    BlankCellRow = 1
    BlankCellColumn = 3
    
    Cells(BlankCellRow, BlankCellColumn).Select
    Cells(BlankCellRow, BlankCellColumn) = 0
    Selection.Copy
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "mm/dd/yyyy"
    Cells(BlankCellRow, BlankCellColumn).Select
    Selection.ClearContents
End Sub

Open in new window