excel problem

[osamaalvi]

hello firends, how are you?

i am facing a big problem in excel(excel 2007)
suppose meray 2 column hain
Column 'A' and Column 'B'
Column 'A' main merged cells hain
A1:A2
A3:A5
A6:A10
A11:A15

ab suppose meray pass column A1:A2 (merged cell) main Value "1" hai
to main chahta hoon kay column B main B1 say B2 tak value "1" aa jaye
remember Column "B" completely unmerged hai

isi tarhan A3:A5 main agar Value "2" hai to column "B" main B3:B5 tak 2 aa jaye

manually to yeh 10 ghantoon main hoga agar app kay pass 2000 raws hoon
lekin agar koi formula ho to easily ho jaye ga
plz help me i m waiting for reply

[Haseeb Alamgir]

ڈئیر آسانی سے آ تو رہی ہے کوئی مشکل بھی نہیں ہے اے۱ اور اے۲ کو مرج کرکے ولیو لکھو پھربی ۱ میں جا کر= کا نشان پرس کرکے اے۱ اور اے۲ سیل کا ریفرینس دے دو۔ کام ہو گیا

[osamaalvi]

tum nay ghoor nahi kiya cell uneven hain
i mean kay Column A main A1:A2 merge hain phir A3:A5 merge hain phir A6:A10 merge hain to agar aik formula lagaya aur neechay copy kia to answer wrong araha hai kuch samjhay

[zulfi721]

osama attach your excel sheet,then v can guide you in a better way that what do u want to ask............

[zulfi721]

Bro check this excel file hope dat it would solve your problem

[sayhellotoit]

Copy your Column A (the merged one) into a new workbook and then paste the following code into Visual Basic Editor [in Office 2003, Tools-->Macro-->Visual Basic Editor]

Option Explicit
Sub Test()

Dim r As Excel.Range
Dim r1 As Excel.Range
Dim myR As Excel.Range

For Each r In ActiveSheet.UsedRange
Debug.Print r.Address, r.Count
If r.MergeArea.Count > 1 Then
Set myR = r.MergeArea
r.UnMerge
For Each r1 In myR
r1.Value2 = myR.Value2
Next r1
End If
Next r

End Sub

Press Run button in Visual Basic Editor
As a result, all cells will be un-merged and duplicate values will be copied into blank cells.
Now simply copy this column and paste it into your original Workbook in column B.

[osamaalvi]

Shukriya app sub ka "zulfi bhai" aap ka bhi but "sayhellotoit'" kay Macros script nay kaam kerdiya koi formula to na mila per kaam hogaya

Actually main aik AutoCAD operator hoon aur mujhay survey data extract kerna perta hai to mujhay is ki zarorat parti hai manually kernay main bohut time lagta hai

Thanks

[osamaalvi]

Thank You My problem solved

sayhellotoit bhai kay macros script nay kaam ker diya Zulfi bhai aap ka bhi shukriya actually main aik AutoCad opearator hoon aur mujhay Survey data extract kerna perta hai manually kernay main buhut time chahiye
However

ThankYou