some vba coding from pseudo code

vofzxy

New Member
Joined
May 2, 2013
Messages
8
good day all, i have a "simple" issue, it's just i do not know how vba works,
i have done basic C /matlab but thats about it

ok here goes i have 2 problems i HOPE to solve

situation: 2 cols of data (54k long ><" ) col A & B
aim: copy data from col B to col A, only if the cell in col A is blank

PSEUDO CODE:

//I KNOW LOL, THANKS FOR STARTING AT 1 NOT 0
VAR X =1


// check cell AX is empty, else copy from cell BX
if sheet.cell.A(X) == isblank();
sheet.cell.A(X) = sheet.cell.B(X)


//check for last row
if sheet.cell.A(X) == isblank() & sheet.cell.B(X) == isblank();
exit

// increase X
X++

//loop?


2nd situation: i have data is MM/DD/YYYY, I want to convert them to YYYYMMDD

i really do not have any idea on this, i am thinking in terms of reading each digit putting them in some LONG CHAR
or individual "BOXES" and reading them backwards

is it possible?

cheers

thanks so much.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Try

Code:
Sub test2()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With
End Sub
 
Upvote 0
For the first part, perhaps this.
Code:
Dim rng As Range

     Set rng = Range("A1")

     While rng.Value <> "" And rng.Offset(,1).Value<>""

           If rng.Value = "" Then
                 rng.Value = rng.Offset(,1).Value
           End If

           Set rng = rng.Offset(1)
     Wend
That's a kind of brute force method, there might be a more elegant way to do.

For the second part, if you have real dates you can just format them to display as you want.
 
Upvote 0
For the first part, perhaps this.

That's a kind of brute force method, there might be a more elegant way to do.

For the second part, if you have real dates you can just format them to display as you want.

heys man, thanks for sharing.
nah the formatting does not work, cause changing from "date" to "general" will produce random numbers or sorts.


EDIT: i must admit something, i do not know what to do with ur code :(
 
Last edited:
Upvote 0
Code:
For Each ACell In Range("B1", Range("B" & Rows.Count).End(xlUp).Row   
    If IsEmpty(ACell.Offset(,-1)) Then
       ACell.Offset(,-1).Formula = ACell.Formula
    End If
Next

As far as the Dates Click on the letter at the top of the Column then right click a cell. then Select "Format Cells..." from the context menu. Under the Number Tab Select Custom and in the text Box enter, "yyyymmdd"
 
Upvote 0
If you have real dates then formatting should work.

Though you would change the formatting to Custom YYYYMMDD not General.

If you format as General you will see the date values as they are actually stored in Excel, not random numbers.
 
Upvote 0
Rich (BB code):
For Each ACell In Range("B1", Range("B" & Rows.Count).End(xlUp).Row)   //syntax error, i added the )
    If IsEmpty(ACell.Offset(,-1)) Then
       ACell.Offset(,-1).Formula = ACell.Formula
    End If
Next

i will try the DATES thing again. cheers

back to the ), i get "method 'range' of object '_global' failed "


**** i need a vba book
 
Upvote 0
Try

Code:
Sub test2()
Dim LR As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
With Range("A1:A" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]"
    .Value = .Value
End With
End Sub

sorry, i did not refresh the page in time,
OMG it works

urm, i can only say thanks , but i wish i could do more.

PS, do you have any pointers how i can get around starting vba/ pivot tables, books perhaps?
honestly, yes it is out of necessity of the job and i am "lazy" that i am asking.
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top